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!
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
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.
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.
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?
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/
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?
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!
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
@@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.
@@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.
Love these easy-to-digest videos to cover missing pieces of knowledge or act as refreshers!
Also, the teaching style. Thank you!
Glad you like the video!
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!
Thanks, glad it was helpful!
love this video. So informative. functional based index was a new concept to me.
Glad you liked it!
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
Great idea! I can create a video on that.
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.
You're welcome! That's a good idea, I do have some other videos on my channel that show the query before and after.
I'll have a look at your videos list.. Thank you, really Appreciate the reply @@DatabaseStar
Thanks your video help me very much! Success to you my friend!
Glad it was helpful!
Thanks for the explanation. It was very very helpful. Now I really understand well indexes.
Glad it was helpful!
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.
Great point! I’ll create a video on this in the future
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?
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/
Super! Many thanks for sharing!
No problem, glad it helped!
very useful, thank you!
You’re welcome!
another helpful video, thanks again
Happy to help!
great intro video, thank you
Thanks!
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?
Good question. Yes it will run automatically when needed, there's no need to create anything else to get it working.
very nice explaination
Thanks!
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!
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
@@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.
B-tree seems very complicated. Or is this caused by the example image?
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.
@@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.
TopNotch
Thanks!