SQL Indexes - Definition, Examples, and Tips
ฝัง
- เผยแพร่เมื่อ 20 พ.ค. 2024
- Indexes in SQL databases are a helpful feature to improve the performance of your queries.
There are a few different types of indexes. Some are common across different vendors, and some are exclusive to one vendor.
In this video, you’ll learn what an index is, see some examples of how they are processed, learn the syntax of creating different indexes, and see some SQL examples of creating indexes on tables.
You’ll also learn some tips on when to create indexes, and why you shouldn’t just create an index on every column.
Timestamps:
00:00 Introduction
00:40 What is an index?
02:13 A database index
02:48 B-tree index
05:01 B-tree index example SQL
06:22 Function-based index
08:12 Clustered index
09:00 Bitmap index
10:33 When should you create an index?
/// RESOURCES
📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🧭 SQL Roadmap from basics to advanced: www.databasestar.com/sql-road...
✍️ Learn how to design a database: databasestar.mykajabi.com/edd
📧 Contact me here: www.databasestar.com/contact/
📝 Database Design Exercises: bbrumm.gumroad.com/l/hqsxi
📝 SQL Practice Questions: bbrumm.gumroad.com/l/ulekqk
📝 SQL Performance Quick Guide: bbrumm.gumroad.com/l/fcvny
📝 Ultimate SQL How-To Guide: bbrumm.gumroad.com/l/oqggau - วิทยาศาสตร์และเทคโนโลยี
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!
Thanks for the explanation. It was very very helpful. Now I really understand well indexes.
Glad it was helpful!
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.
Super! Many thanks for sharing!
No problem, glad it helped!
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/
another helpful video, thanks again
Happy to help!
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
great intro video, thank you
Thanks!
very useful, thank you!
You’re welcome!
very nice explaination
Thanks!
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
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.
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.
TopNotch
Thanks!