No video

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

  Рет қаралды 49,502

Concept && Coding - by Shrayansh

Concept && Coding - by Shrayansh

10 ай бұрын

➡️ Notes link: Shared in the Member Community Post (If you are Member of this channel, then pls check the Member community post, i have shared the Notes link there)
➡️ Join this channel to get access to member only perks:
/ @conceptandcoding
Discussed various points in detail:
- How DBMS stored the data in DB
- How B tree is used for indexing
- What is clustered and Non-clustered Indexing
- How it able to search data faster.
support this channel:
/ @conceptandcoding
#softwareengineer #database #dbms

Пікірлер: 166
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Don't miss this: HLD Basics to Advanced: kzfaq.info/sun/PL6W8uoQQ2c63W58rpNFDwdrBnq5G3EfT7 LLD Basics to Advanced: kzfaq.info/sun/PL6W8uoQQ2c61X_9e6Net0WdYZidm7zooW JAVA Basics to Advanced: kzfaq.info/sun/PL6W8uoQQ2c63f469AyV78np0rbxRFppkx
@drpstar
@drpstar 10 ай бұрын
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.
@revvedupofficial
@revvedupofficial Ай бұрын
no one, i repeat no one, explained like this, thank you so much for uploading these types of indepth videos 🤩
@ConceptandCoding
@ConceptandCoding Ай бұрын
thats a lot buddy 🙏
@shawkiahmed3586
@shawkiahmed3586 Ай бұрын
I would say, this is one of the most amazing explanation i have ever seen on indexing. Previously i only knew that indexing can make search faster but now i understand all the internals about indexing. Thanks so much for your effort. Take love from Bangladesh.
@Shivam-wk6sq
@Shivam-wk6sq 27 күн бұрын
Piece of gem. One of the best videos on indepth indexes.. Thanks for the this video Shreyansh
@NeverGiveUp186
@NeverGiveUp186 10 ай бұрын
Amazing explanation Shrayansh. Absolutely loved it !! If my college professors took even 10% of the efforts taken in this video for explaining the topic, life would have been so much better xD
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks 🙏
@anjaligupta5044
@anjaligupta5044 3 ай бұрын
You are such a good teacher. Everything was so clear. Thanks a lot Shreyansh. :)
@vkhemant
@vkhemant 29 күн бұрын
One of the best videos I ever saw on indexing. Thanks Shrayansh.👌
@Gengar_33
@Gengar_33 8 сағат бұрын
Fun fact: Actually B in B tree is not an acronym for anything. Rudolf Bayer and Edward M. McCreight in 1972 came up with the idea of B tree. "B" was later given a meaning by people, most widely used one being "balanced" Btw Amazing explanation!! Learnt new things and refreshed my concepts
@socialmedia5173
@socialmedia5173 10 ай бұрын
Dude hats off , who put this effort Your channel is most underrated Keep creating ♥️
@srawat1212
@srawat1212 10 ай бұрын
+1
@tanazshaik678
@tanazshaik678 23 күн бұрын
I am glad I found your channel Sir! Respect...
@sravyaparsi7952
@sravyaparsi7952 6 ай бұрын
You have explained the concepts crystal clear. Thank you Shreyansh.
@ConceptandCoding
@ConceptandCoding 6 ай бұрын
thanks
@rishabhagarwal8049
@rishabhagarwal8049 6 ай бұрын
Thanks Shrayansh, I got the Indexing in one go
@sameer_sah
@sameer_sah 10 ай бұрын
Thanks a lot Shreyansh ! Very informative. Watched till the end. Recalled a lot of forgotten concepts 😄 (data blocks, database pages, B/B+ trees, cluster/ un-clusted indexes ) Bookmarking this. Please add 'video chapters' if possible.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@anusuyaganguly6009
@anusuyaganguly6009 10 ай бұрын
In depth explanation in a smooth readable format.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@Rajag-ic1xt
@Rajag-ic1xt Ай бұрын
you are amazing man, It is so clear to understand
@randheer.chauhan.2
@randheer.chauhan.2 10 ай бұрын
Great explanation Shreyansh 👍 For the first time i got to know how indexing really works internally.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
thanks buddy
@AtharvaRao0104
@AtharvaRao0104 10 ай бұрын
Great video! I like it because you have questions before explaining the concept .. that makes us think a bit than just listen passively .. perhaps after the question you can ask the viewer to pause and think .. eg: pause and think how you can make the search faster than O(N) .. just an opinion
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks for the feedback buddy
@user-fl1zk7bz1g
@user-fl1zk7bz1g 10 ай бұрын
Hi Shrayansh, first of all, a big thank you for providing such valuable content. it deepens my curiosity about the internal workings of indexes and B+. I have a small request: could you please host a live session where we can discuss our understanding of the video and implement an index on a table. Thank you
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Sure buddy, i will plan for it.
@anveshkonda8334
@anveshkonda8334 4 ай бұрын
Thanks a lot @Shreyansh
@deepakrana2388
@deepakrana2388 10 ай бұрын
Thanks Shreyansh!! Content is pure Gold 🎉
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thank you
@user-pv4ft1qq2l
@user-pv4ft1qq2l 10 ай бұрын
Finally I can say now I know what is indexing.. Thanks for this video
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Welcome
@zishanshaikh9375
@zishanshaikh9375 5 ай бұрын
Thank you for making this video, very clear & detailed explanation, could you please make a video explaining how composite index containing multiple columns will work ? how the BTree will be created and used for searching
@rakeshverma6867
@rakeshverma6867 21 күн бұрын
Excellent explanation.
@vishalghanghav748
@vishalghanghav748 10 ай бұрын
Shreyansh , Can you please post videos around weekend or keep public upto the weekend whenever posted
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Noted, it make sense Vishal.
@meyankgarg2686
@meyankgarg2686 3 ай бұрын
boss kamal ka explaination hai
@chgeetanjali7919
@chgeetanjali7919 5 ай бұрын
Very informative .. thanks for the video... i had a clear understanding of indexes now
@ConceptandCoding
@ConceptandCoding 5 ай бұрын
thanks
@pranjalsharma7650
@pranjalsharma7650 3 ай бұрын
Really admire your content man!!
@ConceptandCoding
@ConceptandCoding 3 ай бұрын
thanks
@anasyousaf4439
@anasyousaf4439 3 күн бұрын
Great
@DurgaShiva7574
@DurgaShiva7574 10 ай бұрын
Lots of doubts in this video.. please make a live session 🙏
@laymancode4433
@laymancode4433 10 ай бұрын
Just love this type of content . God bless you 💕💕❤
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@KishoreThatavarthi
@KishoreThatavarthi 8 ай бұрын
Thank you Shreyansh for amazing content
@ConceptandCoding
@ConceptandCoding 8 ай бұрын
Thanks
@nawaahkh
@nawaahkh 8 ай бұрын
Thank you a lot for this great content with amazing explanation. 👍
@ConceptandCoding
@ConceptandCoding 8 ай бұрын
Thanks
@SadikSadhi-se7pp
@SadikSadhi-se7pp 9 күн бұрын
Great video Shrayansh! Could you please explain about the composite index working as well?
@arshkatyal2807
@arshkatyal2807 Ай бұрын
Amazing explanation shrayansh. Just 1 question here(for anyone 1 Basic difference we got b/w clustered and non clustered index is that, in clustering, offset maintains the order in data pages in which B+ tree has sorted But what advantage does that offset sorting gave which is not present in non clustered indexes. Please let me know if anything is unclear.
@vaaidooyt1837
@vaaidooyt1837 10 ай бұрын
Shreyansh when you told that you are making it public for only 2 days at that time i downloaded the video as it was long and i want to understand with peace and slow pace as I'm a working professional. Honestly loved the video ❤
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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
@vaaidooyt1837
@vaaidooyt1837 10 ай бұрын
@@ConceptandCoding ❤️❤️❤️❤️ thanks for your precious time
@mahboobhasan2137
@mahboobhasan2137 2 ай бұрын
nice video, very informative.
@akashdutta1620
@akashdutta1620 5 ай бұрын
Data Pages - This is what dbms creates, usually of size 8kb Data page - Header(Page No, Freespace, check sum), Data Records, Offset Array.. For one table, dbms can create multiple data pages. Data pages actually stored in data block in physical memory Dbms have no control in data block, so it maintains a 1:1 mapping of Data Page to Data Block Indexing - It is a technique used to query the database faster. B+ tree is used to implement indexing, it provide O(log n) for searching, insertion, deletion. B+ tree It maintain sorted data All leaf node are at same level M order Tree means, each node can have atmost M children and M-1 key.
@ConceptandCoding
@ConceptandCoding 5 ай бұрын
👍
@akashdutta1620
@akashdutta1620 5 ай бұрын
@@ConceptandCoding Please ignore I am just taking notes
@srawat1212
@srawat1212 10 ай бұрын
Quick question: How does a new column insertion affects the clustered index ? Now since the size of each row has changed...the number of rows that can be accommodated in a page should be less than what it was before. Can you please explain it as well ?
@brajkishore32
@brajkishore32 8 ай бұрын
Adding new column will not affect clustered index. It will affect Data Page/record only
@manoharmanu9240
@manoharmanu9240 5 ай бұрын
Great Explanation 👏👏
@bijanzohouri98
@bijanzohouri98 9 ай бұрын
Very good explanations on indexing
@ConceptandCoding
@ConceptandCoding 9 ай бұрын
Thanks
@IsolatedMonk
@IsolatedMonk Ай бұрын
Thanks Shrayansh for this amazing explanation, qq: who does the conversion from a data page to a data block?
@vinaysharma7072
@vinaysharma7072 4 ай бұрын
best video database index
@kushalunune2084
@kushalunune2084 10 ай бұрын
Thanks for the incredible content. Iit wouls also be helpful if you provide a short segment of links/books/articles you used while studying these topics.
@mahanteshambali
@mahanteshambali 10 ай бұрын
I really liked the video. Thank you for your work. Could you please point to resources you used for this video. Like Books or Blogs it would be helpful.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks,to be honest, most of my learning is through working and by giving interviews.
@mahanteshambali
@mahanteshambali 10 ай бұрын
@@ConceptandCoding thank you
@2412_Sujoy_Das
@2412_Sujoy_Das 10 ай бұрын
This was probably gonna be the 5 star video according to me, but after 1:10:00 mins, you hastily explained everything shreyansh which is the last thing any beginner would want.... Anyways nice explanation 👍
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks for the feedback, non clustered index and index Pages right, i will explain in separate video buddy, thanks for the feedback
@arpit3640
@arpit3640 6 ай бұрын
Very good explanation Shreyansh 👍
@ConceptandCoding
@ConceptandCoding 6 ай бұрын
thanks
@sukritiguin5637
@sukritiguin5637 6 ай бұрын
You gave more than your 100%. ❤❤
@ConceptandCoding
@ConceptandCoding 6 ай бұрын
thanks
@najimali32
@najimali32 10 ай бұрын
Amazing video Shreyansh 🎉
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@buntykumar7270
@buntykumar7270 3 ай бұрын
Nice notes
@SuperLucasGuns
@SuperLucasGuns 6 ай бұрын
nice. subscribed.
@syamreddy-oq7cz
@syamreddy-oq7cz Ай бұрын
Hi Shrayansh, Explanation is really amazing, One query : Why the page split happens? What is the need of it.
@ConceptandCoding
@ConceptandCoding Ай бұрын
Page splits occur in databases to maintain the structure and efficiency of indexes. When an index page becomes full and a new entry needs to be inserted, the page is split into two to accommodate the new data. This ensures that the index remains balanced and efficient for fast data retrieval.
@MDJahidHasan-de8bk
@MDJahidHasan-de8bk Ай бұрын
brother it is a great lecture and It is very much understable. But I have a doubt about the data page section. In which time the data page is created, -- when the B+ tree (indexing) is created or when user first time create data. Or different data page is created during the B+ tree formation, or during creating index(B+ tree) the old data page is updated? I am not clear about this part. I am waiting for your reply and again your lecture is awsome.
@Siddik-ko8ol
@Siddik-ko8ol 4 ай бұрын
Thanks a lot!!
@bikeshregmi2392
@bikeshregmi2392 5 ай бұрын
nice
@arishtajain8372
@arishtajain8372 2 ай бұрын
You missed one crucial point while explaining page splitting. The actual data records within the data pages themselves do not rearrange or move during the split operation, unless it is happening on the clustered index, because in the case of clustered index the DBMS needs to store the data records in the sorted order of the index, otherwise why would it care about the order in which the data records are stored if the B+ tree is on a non clustered index.
@lokendrasingh5805
@lokendrasingh5805 8 ай бұрын
Great explanation. Love it. Can you please explain how the compound index(name, address)is stored in the b+ tree? also, just one small favor by mentioning which drawing software is used here.
@lokendrasingh5805
@lokendrasingh5805 8 ай бұрын
@Conceptandcoding Please confirm
@boerliu3668
@boerliu3668 4 ай бұрын
you are awesome!
@drpstar
@drpstar 10 ай бұрын
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.
@godbhaigod
@godbhaigod 2 ай бұрын
Even he dont know😂😂
@sahilgarg3460
@sahilgarg3460 10 ай бұрын
Good one, thinking to find same topic with some good explanation found it here.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@tejasshaha6629
@tejasshaha6629 10 ай бұрын
Very very good video. Just 1 question - How costly it is for DBMS if we are inserting 1 row and it is resulting into multiple page splits ?
@arunpandya401
@arunpandya401 10 ай бұрын
Hi Shreyansh, thank you for the detailed explanation. I have one doubt: If we are creating clustered and non-clustered indexes, how will it perform page split? As per my understanding, it will always try to put the nearest B+ Tree node values in one data page. Now it is certain that for clustered and non-clustered index B+ Trees values there is a conflict in storing rows in data pages.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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.
@aayush7099
@aayush7099 10 ай бұрын
Amazing video
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks
@balakrushnabehera2800
@balakrushnabehera2800 10 ай бұрын
Hi Shreyansh amazing video i watched to the end but i think more insights on index table is needed because when it is around 1:19:34 you mentioned about index table prior to that there is no mentioning of index tables/pages. And i felt like when we execute a search query how the procedure follows from beginning needs to be explained starting from index pages.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
I have explained in end the sequence when query comes. Sure I will explain Index pages more through short videos
@balakrushnabehera2800
@balakrushnabehera2800 10 ай бұрын
@@ConceptandCodingthanks 👍
@amanvikram04
@amanvikram04 Ай бұрын
Hi shreyansh, thanks for very detailed explanation on database indexing , i just want to know do you have any video on sharding or not , if yes then please help to redirect if not then request you to make a video on it please ,Thank you so much
@ConceptandCoding
@ConceptandCoding Ай бұрын
its not there yet, i will make
@amanvikram04
@amanvikram04 Ай бұрын
@@ConceptandCoding thank you so much
@PradeepSingh-ov3bt
@PradeepSingh-ov3bt 8 ай бұрын
Just a quick question , if i have multiple non-clustered column indexes in a table I am writing a query which includes these columns in where condition , now dbms will use which index here ? In below example merchant_id, date_created and order_id all three are non-clustered indexes select * from order where source_id = 'xyz' and merchant_id ='xyz' and date_created >= (NOW() - INTERVAL 30 MINUTE) and order_id like "pf_%"
@karmicveda9648
@karmicveda9648 10 ай бұрын
🔥🔥🔥
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Thanks:)
@sumitroy7817
@sumitroy7817 10 ай бұрын
hey, nice explanation. thank you so much. can you please explain ACID and normalisation too.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
sure
@DevanshChiluka
@DevanshChiluka 4 ай бұрын
@ayushiagarwal2306
@ayushiagarwal2306 10 ай бұрын
Thanks for a great video. I had one query : At what time is offset stored in data pages in case of clustered index? Is it when a data page is full or is it at insertion of each row? How is the order maintained in offset?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
With every row insertion, offset is also updated. And order is maintained according to order of clustered index.
@kolasphoorti1030
@kolasphoorti1030 10 ай бұрын
Hi Shreyansh, really a good video, helped in understanding index in depth. I have 2 questions: 1. I did not really understand how page splitting is happening here. Is it based on the order of index? (ascending order). if yes, is it really needed? We can just put it in next free page and maintain pointers to the data pages. 2. In non-clustered indexing, I don't understand how the data is accessed in O(logN). The accessing of data page from B+ tree as I understand is O(logN), but there is no pointer to the row inside data records of the data page, as a result it should scan whole data page as In understand? In clustered indexing though, since the order of index is maintained in offset, we can use a binary search to get to the correct row given the index value. I was always assuming along with the data page mapping in B+ tree, there should be some kind of map which has key as index value(column value) and value as pointer within data page.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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)
@kolasphoorti1030
@kolasphoorti1030 10 ай бұрын
@@ConceptandCoding got it. Thank you
@adityaallam
@adityaallam 5 ай бұрын
Great Explanation sir! why cannot hashmap be used instead of B + trees for indexing?
@ConceptandCoding
@ConceptandCoding 5 ай бұрын
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.
@adityaallam
@adityaallam 5 ай бұрын
@@ConceptandCoding Thank you :)
@harshitagarwal2682
@harshitagarwal2682 Ай бұрын
👍👍
@PawanKumar-cw9ko
@PawanKumar-cw9ko 7 ай бұрын
Hi Shreyansh, What is an index page? Is it the same as a data page or something else?
@ConceptandCoding
@ConceptandCoding 7 ай бұрын
mostly same as Data pages, but stores indexing related information
@anishtaneja5665
@anishtaneja5665 10 ай бұрын
Shreyansh , video is amazing . Only point regarding non clustered index , it’s not clear how it is referencing page / row for retrieval ?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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.
@anishtaneja5665
@anishtaneja5665 10 ай бұрын
@@ConceptandCoding . Thnx !!
@MDSAJID-vh6ho
@MDSAJID-vh6ho 7 ай бұрын
@Conceptandcoding where this index itself store? How DBMS know the location where index is stored?
@ConceptandCoding
@ConceptandCoding 7 ай бұрын
it creates index pages
@clutchh_god
@clutchh_god 8 ай бұрын
does page splitting happens for non-clustered index also?
@Aakashkumar-re7gk
@Aakashkumar-re7gk 8 ай бұрын
great video one doubt b/w clustered and non clustered clustered means create index on a primary key non clustered means create index on other keys the example in the video where we create a clustered index on empId which is ok but when we create non clustered index on employee name I have a doubt Problem because you said when there is an entry in b+ tree to choose a paricualry data page it sees to its neigbour data page if empty insert otherwise split it Doubt is now we have two b+ trees one based on Id and other on name if first binary tree say row will go to data page 1 and other b+ tree says row will go to page 2 in which page we will make an entry?
@RJ-nt3ol
@RJ-nt3ol 10 ай бұрын
@Shreyansh, If we have not added indexing first, then data pages will be stored. Now if we add indexing, then all those data pages will again be refactored as per the indexing. Am I right?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Yes
@ancyzachariah5491
@ancyzachariah5491 17 күн бұрын
How to get notes of this indexing topic
@aadityaupadhyay6264
@aadityaupadhyay6264 10 ай бұрын
Nice tutorial, I have one doubt How is the order defined of B+ tree in the DB? Here you have taken 3, in real case scenario on what basis it will decide?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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.
@aadityaupadhyay6264
@aadityaupadhyay6264 10 ай бұрын
@@ConceptandCoding thanks for clarifying.
@gurnoorsingh2954
@gurnoorsingh2954 9 ай бұрын
Does clustered index also uses B+ tree? Because it can use the offset concept in a single data page. But apart from that I believe it needs to use B+ tree. Can someone confirm?
@sanchayyadav5308
@sanchayyadav5308 6 ай бұрын
Shrayansh I think Physical memory is RAM not a disk(ROM)🧐
@girishanker3796
@girishanker3796 2 ай бұрын
⭐⭐⭐⭐⭐
@aayushsingh3140
@aayushsingh3140 4 ай бұрын
why use b+ tress instead of hashmap
@sunilpatel6542
@sunilpatel6542 10 ай бұрын
Hey buddy just want to know in composit index I have table that have index on (a,b) column - If I do where condition only on a column will dbms use (a,b) index - And how B-tree store composit index
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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)
@mcspraint4591
@mcspraint4591 2 ай бұрын
Can you please give me a first time offer on LLD HLD members only resources? I immediately need it.
@kratikharchandani7418
@kratikharchandani7418 10 ай бұрын
Hi, this video was good but i didn't get what is non clustered index?
@kratikharchandani7418
@kratikharchandani7418 10 ай бұрын
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?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Right
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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
@kratikharchandani7418
@kratikharchandani7418 10 ай бұрын
@@ConceptandCoding Thanks for clearing the doubt
@devpratapsingh7050
@devpratapsingh7050 4 ай бұрын
Can we have a common place for all notes link wrt to playlist
@ConceptandCoding
@ConceptandCoding 4 ай бұрын
i did, pls check member community post section, you will get all notes playlist wise.
@clutchh_god
@clutchh_god 10 ай бұрын
what is the advantage of clustered index over non-clustered. Since in the both the cases, index will be pointing to the row's data page. Basically my doubt is, what is the added advantage of having offsets in same order as that of index, since index won't be aware of offset array index it needs to refer to for accessing the row
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
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.
@clutchh_god
@clutchh_god 10 ай бұрын
@@ConceptandCoding got it.. but what is the advantage of having offset array in same sequence as of clustered index?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
@@clutchh_godone of the advantages it gives during range search query.
@priyankam1271
@priyankam1271 10 ай бұрын
Hey, I am not getting Join for you channel to access exclusive content. Please help.
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
kzfaq.info/love/DJ2HAZ_hW-DMJj_U0zN38wjoin
@billion_dreams__7787
@billion_dreams__7787 10 ай бұрын
Bhaiya payment Ka option hi nahi aa raha hai java vala, kuch process batao kaise payment karna hai
@pos1421
@pos1421 10 ай бұрын
can you share the pdf of video ? for future revision
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Pls check the description section buddy
@Mohd_Rashid_Siddiqui
@Mohd_Rashid_Siddiqui 10 ай бұрын
Can you please share the notes link?
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Yes I will put in description section by eod
@subbunittala2012
@subbunittala2012 8 ай бұрын
Hi
@raghunathhembram9962
@raghunathhembram9962 3 ай бұрын
bakwash faltu
@021gauravverma5
@021gauravverma5 10 ай бұрын
Bro if you have speak English so please speak clearly
@ConceptandCoding
@ConceptandCoding 10 ай бұрын
Sure. Pls suggest some points where I can improve.
@logicalrisi
@logicalrisi 6 ай бұрын
Notes achhe nhi hai
System Design: Concurrency Control in Distributed System | Optimistic & Pessimistic Concurrency Lock
1:04:45
Understanding B-Trees: The Data Structure Behind Modern Databases
12:39
Идеально повторил? Хотите вторую часть?
00:13
⚡️КАН АНДРЕЙ⚡️
Рет қаралды 17 МЛН
小丑和奶奶被吓到了#小丑#家庭#搞笑
00:15
家庭搞笑日记
Рет қаралды 8 МЛН
黑天使遇到什么了?#short #angel #clown
00:34
Super Beauty team
Рет қаралды 39 МЛН
Little brothers couldn't stay calm when they noticed a bin lorry #shorts
00:32
Fabiosa Best Lifehacks
Рет қаралды 17 МЛН
SQL Index |¦| Indexes in SQL |¦| Database Index
9:57
Socratica
Рет қаралды 613 М.
Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF
28:34
Decomplexify
Рет қаралды 1,8 МЛН
6 SQL Joins you MUST know! (Animated + Practice)
9:47
Anton Putra
Рет қаралды 138 М.
How do indexes make databases read faster?
23:25
Arpit Bhayani
Рет қаралды 59 М.
Database Indexing for Dumb Developers
15:59
Laith Academy
Рет қаралды 51 М.
SQL Indexes - Definition, Examples, and Tips
12:14
Database Star
Рет қаралды 74 М.
Clustered vs. Nonclustered Index Structures in SQL Server
8:04
Voluntary DBA
Рет қаралды 650 М.
SQL Explained in 100 Seconds
2:23
Fireship
Рет қаралды 814 М.
Идеально повторил? Хотите вторую часть?
00:13
⚡️КАН АНДРЕЙ⚡️
Рет қаралды 17 МЛН