SQL Indexes - Definition, Examples, and Tips

[@szilardfineascovasa6144]

Love these easy-to-digest videos to cover missing pieces of knowledge or act as refreshers! Also, the teaching style. Thank you!

[@DatabaseStar]

Glad you like the video!

[@henriquelisboa3381]

Thank you very much! I'm a newbie with databases but I need to work with one for a project and this video has the perfect balance between easy to understand and digging into advanced concepts! Wish you the best!

[@DatabaseStar]

Thanks, glad it was helpful!

[@tomservo75]

This is really nice, would you ever consider making a more advanced version, for people who know what indexes are but need advice on where to create them, what columns to add, single vs. multi-column

[@DatabaseStar]

Great idea! I can create a video on that.

[@ItsWithinYou]

Super! Many thanks for sharing!

[@DatabaseStar]

No problem, glad it helped!

[@rafaelbraga3d]

Thanks for the explanation. It was very very helpful. Now I really understand well indexes.

[@DatabaseStar]

Glad it was helpful!

[@Felipe_Matheus_1991]

Thanks your video help me very much! Success to you my friend!

[@DatabaseStar]

Glad it was helpful!

[@WaliSayed]

very useful, thank you!

[@DatabaseStar]

You’re welcome!

[@arunajay8497]

great intro video, thank you

[@DatabaseStar]

Thanks!

[@nicholassmith6412]

another helpful video, thanks again

[@DatabaseStar]

Happy to help!

[@weekendprogrammer7545]

Thank you for this video, very helpful and explained so that any one can understand. I know it's a bit more to ask, but if it's possible to actually see the reduction in the time taken by actually running the queries in some database would be great.

[@DatabaseStar]

You're welcome! That's a good idea, I do have some other videos on my channel that show the query before and after.

[@weekendprogrammer7545]

I'll have a look at your videos list.. Thank you, really Appreciate the reply @@DatabaseStar

[@omega.developer]

very nice explaination

[@DatabaseStar]

Thanks!

[@krs-tube]

Thanks for the intro vid. You mentioned you're going to explain when to use clustered vs non-clustered index, but you didn't do it. Could you share your thoughts.

[@DatabaseStar]

Great point! I’ll create a video on this in the future

[@siddharthapenchala8146]

Thanks for the brief Explanation, Sir. Can you please clarify my doubt. Sir, I have created an index but the optimizer is not using it? What are Such cases where optimizer doesn't use the index?

[@DatabaseStar]

It depends on the database, but generally it's because the database has found a better way. It could be the columns in the index do not match the query. It could be the distribution of the data means the index won't help/

[@8485Rockstar]

Great.......I have one question I don't know is it valid or not. Suppose I created one index on the primary key of one table, Will it run automatically, or need to create any job for that?

[@DatabaseStar]

Good question. Yes it will run automatically when needed, there's no need to create anything else to get it working.

[@datalore6187]

Does order matter when choosing which columns in the WHERE clause? Say for example I have a primary key index of (ID, LOCATION). There will be many ID's in the table, but only 5 LOCATION values. In my SELECT statement, should I use "SELECT * FROM EMPLOYEES WHERE ID = :P_ID_PARM AND LOCATION = :P_LOCATION_PARM" or should I use "SELECT * FROM EMPLOYEES WHERE LOCATION = :P_LOCATION_PARM AND ID = :P_ID_PARM"? Does it matter? Thanks!

[@DatabaseStar]

Good question! No, I don't think the order of the WHERE clause matters (at least I haven't seen or read anything about this being considered by the database). The order of columns when creating an index with multiple columns definitely matters, but in your example I think both queries would be the same. You could check the execution plan for both queries and see what the differences are

[@datalore6187]

@@DatabaseStar Thank you! Interestingly enough, I ran a query both ways. It turns out that SELECTing on the column with the most values first speeds things up. So in my example, if I first SELECT on the ID column, and then SELECT on the LOCATION column, the results are faster than if I first SELECT on the LOCATION column, and then the ID column. Just wanted to share.

[@RoysIdea]

B-tree seems very complicated. Or is this caused by the example image?

[@DatabaseStar]

It could just be the example image. I include it to explain how it works, but for most of the time we don't really need to know how it works.

[@Kleberei]

@@DatabaseStar Because that example image does not visually represent a B-Tree. Every node, except for the root and the leaves, has at least ⌈m/2⌉ children and values do not repeat in a B-tree.

[@apurvatripathi7633]

TopNotch

[@DatabaseStar]

Thanks!