21. Database Indexing: How DBMS Indexing done to improve search query performance? Explained 

Postgresql use heap tables and doesn't have concepts like clustered index. What is your thoughts on that? How indexing works there and what are the pros and cons of these approaches? I asked a lot of questions😅, please reply if possible. Thanks for the detailed explanation.

Thanks 🙏

+1

Thanks

thanks

Thanks

thanks

Thank you

Noted, it make sense Vishal.

Thanks for the feedback buddy

Adding new column will not affect clustered index. It will affect Data Page/record only

Sure buddy, i will plan for it.

thanks buddy

Thanks

Welcome

Thanks

Thanks

thanks

Thanks

Thanks,to be honest, most of my learning is through working and by giving interviews.

@@ConceptandCoding thank you

thanks a lot. actually got many msgs to keep it till weekend as during weekend only they will get time to watch. So till weekend i will keep. Take your time to understand and watch buddy

@@ConceptandCoding ❤️❤️❤️❤️ thanks for your precious time

👍

@@ConceptandCoding Please ignore I am just taking notes

With every row insertion, offset is also updated. And order is maintained according to order of clustered index.

Yes

Thanks

Thanks

Okay consider this, Data page is mostly pointed by Clustered index nodes Non clustered index points to clustered index and from their it goes to data page. 2 hop it has to do. But in some DB, non clustered index also points to data page. But I did not understand when you say conflict? Insertion always happens based on clustered index.

sure

Thanks for the feedback, non clustered index and index Pages right, i will explain in separate video buddy, thanks for the feedback

thanks

There are 2 flavours of Non clustered index: - it points to clustered index and from there it goes to data page(2hop) - it also directly pointing to data page. Depends upon DB to DB. They Store the reference where to look up for the data.

@@ConceptandCoding . Thnx !!

I have explained in end the sequence when query comes. Sure I will explain Index pages more through short videos

@@ConceptandCodingthanks 👍

thanks

@Conceptandcoding Please confirm

This Changes from DB to DB buddy. Depends on many factors one such factors is size of Data Page and size of data blocks. Based on such factors it compute and decides what Order B tree it has to create.

@@ConceptandCoding thanks for clarifying.

For composite index it will create B+ tree just by concatenation both a,b column values. So in where clause, it would be able to use B+ tree when in search query where clause if you provide either: a (able to search in B tree) a and b ( able to search in b tree) b (won't be able to search in B tree, bcoz while creating B tree it first uses a)

For Non clustered index, there are 2 flavours available depends upon DB to DB. - 1st which i mentioned, you can have many non clustered index key + clustered key also point to data page. - 2nd flavour is, we can have many non clustered key, but Non clustered key point to Clustered key index first and using clustered index they find the data page. So it's 2 hop. But we are always sure with clustered index is we can get the respective data page in one hop. Nice question btw.

@@ConceptandCoding got it.. but what is the advantage of having offset array in same sequence as of clustered index?

@@clutchh_godone of the advantages it gives during range search query.

mostly same as Data pages, but stores indexing related information

Page splitting again is a very interesting topic to understand. Since you asked, i will try to explain why page split is done instead of just create a new page. Actually when DBMS first select the most appropriate Data Page for the new item to put and there is no space, it will create new data page and let say adds the new item in the newly created data page, but it also does one more thing, that in 1st data page it also adds the address of newly created data page (so it has to split some item which is present in 1st page to new data page). That's why when we say, during page split it divides the rows bcoz DBMS stores pointer of newly data page in existing data page, so it need some space. Second regarding Non-Clustered Indexing, in most of the DBs it first point to Clustered index and then fetch the data page, so it's kind of 2 hop. (And regarding O(logn) search, it can find the correct data page in O(logn) and searching the row inside a data page is just constant time as data page size is fixed)

@@ConceptandCoding got it. Thank you

it creates index pages

Thanks

HashMaps are not suitable for indexing in all scenarios because they lack the ability to efficiently support range queries and ordered traversal, which are essential features provided by B+ trees. B+ trees maintain sorted order of keys, making them ideal for range queries and efficient traversal, whereas HashMaps do not guarantee any specific order of keys.

@@ConceptandCoding Thank you :)

Thanks:)

Pls check the description section buddy

also clustered index is primary key only so that we can sort the data according to it and store it and if no primary key is there then it creates an internal index which uniquely identifies a row, is my understanding coorect?

Right

Non clustered index is used for indexing on Secondary key or composite key. Rows or data pages are not ordered based on this index. And many times based upon DB, you will find that instead of directly pointing to data page it first point to Clustered index and through clustered index it goes to data page, so kind of 2 hop

@@ConceptandCoding Thanks for clearing the doubt

ru-vid.com/show-UCDJ2HAZ_hW-DMJj_U0zN38wjoin

Yes I will put in description section by eod

i did, pls check member community post section, you will get all notes playlist wise.

Sure. Pls suggest some points where I can improve.