SQL Indexes - Definition, Examples, and Tips

Подписаться 51 тыс.

Просмотров 66 тыс.

50% 1

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
🎓 Improve your SQL and database skills with Database Star Academy: www.databasestar.com/dsa/
📧 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

Наука

Опубликовано:

17 май 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 36

@szilardfineascovasa6144 11 месяцев назад

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

@DatabaseStar 11 месяцев назад

Glad you like the video!

@henriquelisboa3381 4 месяца назад

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 4 месяца назад

Thanks, glad it was helpful!

@rafaelbraga3d Год назад

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

@DatabaseStar Год назад

Glad it was helpful!

@ItsWithinYou Год назад

Super! Many thanks for sharing!

@DatabaseStar Год назад

No problem, glad it helped!

@nicholassmith6412 Год назад

another helpful video, thanks again

@DatabaseStar Год назад

Happy to help!

@tomservo75 10 месяцев назад

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 10 месяцев назад

Great idea! I can create a video on that.

@siddharthapenchala8146 2 года назад

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 2 года назад

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/

@WaliSayed 5 месяцев назад

very useful, thank you!

@DatabaseStar 5 месяцев назад

You’re welcome!

@weekendprogrammer7545 6 месяцев назад

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 6 месяцев назад

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 6 месяцев назад

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

@arunajay8497 Год назад

great intro video, thank you

@DatabaseStar Год назад

Thanks!

@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.

@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.

@omega.developer 2 года назад

very nice explaination

@DatabaseStar 2 года назад

Thanks!

@krs-tube 4 месяца назад

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 4 месяца назад

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