SQL Indexes - Definition, Examples, and Tips

Поділитися
Вставка
  • Опубліковано 6 лис 2024

КОМЕНТАРІ • 41

  • @szilardfineascovasa6144
    @szilardfineascovasa6144 Рік тому +13

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

  • @henriquelisboa3381
    @henriquelisboa3381 9 місяців тому +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
      @DatabaseStar  9 місяців тому

      Thanks, glad it was helpful!

  • @ArchitecturalAesthetics2046
    @ArchitecturalAesthetics2046 2 місяці тому +1

    love this video. So informative. functional based index was a new concept to me.

  • @tomservo75
    @tomservo75 Рік тому +4

    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
      @DatabaseStar  Рік тому +1

      Great idea! I can create a video on that.

  • @krs-tube
    @krs-tube 10 місяців тому

    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
      @DatabaseStar  10 місяців тому

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

  • @weekendprogrammer7545
    @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
      @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
      @weekendprogrammer7545 11 місяців тому

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

  • @siddharthapenchala8146
    @siddharthapenchala8146 2 роки тому +1

    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
      @DatabaseStar  2 роки тому +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/

  • @8485Rockstar
    @8485Rockstar 2 роки тому

    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
      @DatabaseStar  2 роки тому

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

  • @Felipe_Matheus_1991
    @Felipe_Matheus_1991 4 місяці тому

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

  • @datalore6187
    @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
      @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
      @datalore6187 Рік тому +3

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

  • @rafaelbraga3d
    @rafaelbraga3d 2 роки тому

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

  • @RoysIdea
    @RoysIdea 11 місяців тому

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

    • @DatabaseStar
      @DatabaseStar  11 місяців тому

      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
      @Kleberei 5 місяців тому

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

  • @WaliSayed
    @WaliSayed 11 місяців тому

    very useful, thank you!

  • @ItsWithinYou
    @ItsWithinYou Рік тому +1

    Super! Many thanks for sharing!

  • @arunajay8497
    @arunajay8497 Рік тому

    great intro video, thank you

  • @nicholassmith6412
    @nicholassmith6412 Рік тому

    another helpful video, thanks again

  • @omega.developer
    @omega.developer 2 роки тому

    very nice explaination

  • @apurvatripathi7633
    @apurvatripathi7633 Рік тому +1

    TopNotch