Get my Fundamentals of Database Engineering udemy course to learn more , link redirects to udemy with coupon applied database.husseinnasser.com

9:08 "5080" A good example of how difficult it is to get good benchmarks. ID=5000 was fetched, but why was fetching 5080 so fast while fetching 7080 was slow again? Because PostgreSQL stores rows in pages, which are 8KB blocks of diskspace. Hussain made rows of 2 integer and three(?) characters so one 8KB block can hold about 1000 rows. When the database fetched the page that 5000 was in, that page was cached by the operating system (not the database) had inadvertently instantly cached 1000 rows around id=5000. 14:04 "The primary key is usually stored with every single index" I have never heard of that behavior. Primary keys are always indexed but as far as I am aware they are never automatically added to every index you create. ht index contains tuple and row information to enable a lookup but not the PK. I get the feeling you're seeing the ID rather quicky because the pages were already in cache from the previous queries. And about the LIKe not using an index; that's a good topic for a separate video: Trigram indexes.

I was just wasting time in youtube and suddenly your video pop up to my screen. Good information summarized. Thanks man! Take my like and keep uploading videos!!!

Man, I absolutely love your attitude and style of teaching. Deeply grateful for your content... thank you sir!

Thank you so much! This was clear, concise and very helpful! More postgres tutorials plz!

You're orders of magnitude better than the TA for my DB course! Thank you very much for the explanation 😊

Me: Man I'm really interested in (insert subject here). I wonder if there's a video on this. *Hussein has entered the chat*

Great video! You talked about using a multicolumn index as a way to save time to not have to go to disk. It would be interesting to have a video showing the tradeoffs of this approach. It seems Postgres do not recommend using a multicolumn index, only when really necessary.

I have learned in this video more than I learned in a complete university semester الله يحفظك ❣

Love this video. Really fascinating learning about the intuition behind indexes!

Thank you for the video it was really helpful to watch. I am working with databases and since you already spoke about acid, indexing and pooling another topic I'd be very interested in is views. How and when they are computed their benefits over regular queries and also materialized views which I think is a great Postgre feature.

You explaining skills are just excellent.

wow, exactly what i am looking for! awesome, Hussein.

just finished my first proper postgresql view that takes about 10 seconds and i see hussain nasser upload this video, coincidence? i think not...

This was SO GOOD, congratulations, highest quality class

You are a gift from God for us backend developers.

Best introduction video on indexing I've seen. I feel like I finally understood the concept. Thabks. The "like" expression was a great addition.

in this quick video I jumped into Database Flow world!!! really appreciate your work, bro

Love you my guy, most valuable tech content creator in youtube.

perfect explanation! thanks Hussein...

Started today, My 15th video in a row. Thanks a lot man, getting all this knowledge for free is a blessing for us.

Really good video, well explained and just the right degree of details that I was looking for!

You are an amazing teacher. Thank you!

thank you for this! I love these videos that actually show how theory works in a concrete example!

Thanks Hussein! Amazing content!

Actually normally I would never say something about the accent that anyone has, but wow bro your accent is perfect! Thank you for creating so much valuable content on youtube like this and please keep doing it!

Good stuff. I'm a DBA of about 20 years. I remember using PostgreSQL before the SQL interface was added. :D Anywho, you mentioned the primary key being stored with the data. It's actually the opposite. The data is stored or "clustered" with the primary key. It's the only index that exists with the data. All others are index lookups that reference the location of the data. Great explanation.

Hi, As far as I know, PostgreSQL includes the primary key along with the secondary index. Now, I have a table - tbl_questions that has: 1. id - primary key 2. question_set_id - secondary index I am using the query: EXPLAIN ANALYZE SELECT * FROM tbl_questions WHERE question_set_id = 3 AND id > 50 LIMIT 10; This query is doing an Index scan on question_set_id and then filtering out records where id > 50 Here's the output: Limit (cost=0.14..7.89 rows=1 width=582) (actual time=0.009..0.009 rows=0 loops=1) -> Index Scan using tbl_questions_question_set_id_idx on tbl_questions (cost=0.14..7.89 rows=1 width=582) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (question_set_id = 3) Filter: (id > 50) Planning Time: 0.073 ms Execution Time: 0.021 ms (6 rows) My question is, if the id is stored along with question_set_id, then why is the condition not like Index Cond: (question_set_id = 3) AND (id > 50) I have tried switching the position for id and question_set_id in the query but still the same result. However, when I created a composite index like below, it was working as expected: 1. id - primary key 2. question_set_id, id- secondary index Here's the query and output: EXPLAIN ANALYZE SELECT * FROM tbl_questions WHERE question_set_id = 5 AND id > 10 LIMIT 10; Index Scan using tbl_questions_question_set_id_idx on tbl_questions (cost=0.14..8.01 rows=1 width=582) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: ((question_set_id = 5) AND (id > 10)) Planning Time: 0.074 ms Execution Time: 0.021 ms (5 rows) It will be very helpful if you can clear this out or let me know if I am doing anything wrong. Thanks

Great video and teaching skills. Inspired me to buy your udemy course on databases. Can't wait to learn more.

Well done! Congrats 👏

Thanks for your time! Great intro to indexes

The best channel on youtube!

Thank you Hussein for this video, was waiting for this one.

Thank you so much, Hussein! This explanation is so incredible! After all, i'm asking you if you can explain where index is bad to database (sparse tables, how much it can cost to database size, etc), this will be good at all, and i will be gratefull!

Amazing video, glad to watch this and understand at first glance. I am surely gonna watch more videos to enhance my technical skill.

Thank you so much! very helpful :) this channel deserve Million of subscriber

Great explanation. Thanks

Nasser, Your videos are really informative and it helps me picture the topic . More power to you , god bless

Really great job bro, thanks for all this information.

That was a really good explanation!

This was simply wow 👏🏻

Checkout my Introduction to Database Engineering Udemy course database.husseinnasser.com Chapters intro 0:00 What is an index 0:30 Describe Table 2:15 SELECT [ID] WHERE ID 4:00 SELECT [NAME] WHERE ID 6:30 SELECT [ID] WHERE [NAME] (No index) 9:20 SELECT [ID] WHERE NAME LIKE 11:12 CREATE INDEX ON [NAME] 12:00 SELECT [ID] WHERE NAME (Indexed) 12:50 SELECT [1D] WHERE NAME LIKE (Indexed) 14:30 Summary 16:00

Amazing video, thanx Hussein

Thanks. It is a really amazing lesson I have learnt from this discussion.

It's a great and very helpful video on indexing in general. Please may I get a link if you have uploaded a video on multicolumn indexing? How is done specifically in PostgreSQL? Thank you

Thanks you so much for such a details explanation

So interesting! Could u do one explaining how spatial indexes work on geographic data?

Quite explanatory 👍🏻

Thanks a lot Hussein. Great explanation

Excellent video, thanks!

this was a nice demonstration of indexing, thanks!

Thank you for clarifying concept database indexing. I appreciate your insights, but I'm still a bit puzzled about a few things. While I understand that traditional relational databases have robust implementations of indexing, I'm curious to know why one might opt for search engines, like Elasticsearch, over them. Specifically, how does Elasticsearch indexing differ from that of relational databases? Moreover, are there specific challenges or limitations associated with relational database indexing that Elasticsearch indexing can address more effectively? I'd greatly appreciate any further insights you can provide on this topic. Thank you in advance for your time and assistance.

Really good video, excellent explanation

clearly explained thank you man

Great video! Helped a lot.

Just perfect! Thank ya!

Very helpful. Thank You. Subscribed to the channel.

Waiting for the next indexing video.

It really helped, Thank you.

This was extremely helpful. One of those topics that is never really explained in detail!

Awesome explanation

Really great video. Wasnt on nonclustered vs clustered but really great explanation nevertheless

great video

Thanks for indexing make simple for a layman.... It's complicated but this video made it baby job

thanks!! you helped me so much!

Thanks, that was really clear intro, can I have a suggestion, I know this is not suppose to be a formal education channel and you are reflecting on different topics of backending it's really dull but can you have serieses of same topics in one place, even if you just touched the topic, it will be more beneficial if we could know the different aspects related to a topic.. Again thanks for the intro

Thank you for this informative video❤. You're worth subscribing❤

**Highlights**: + [00:00:00] **Introduction to database indexing** * What is an index and why it is useful * How indexes are built and stored * Examples of index types: B-tree and LSM tree + [00:03:00] **Querying with and without indexes** * How to use explain analyze to measure query performance * How to compare the execution time and cost of different queries * How to avoid full table scans and use index scans instead + [00:12:08] **Creating an index on a column** * How to create a B-tree index on a name column * How to use the index to speed up queries on the name column * How to avoid going to the heap and use inline queries + [00:16:30] **Querying with expressions and wildcards** * How expressions and wildcards prevent the use of indexes * How to avoid using like with percentage signs * How to use hints to force the use of indexes

Great content!!! Thanks!

super cool, thanks for this

Thanks for this video. Please also make a video on everything about SQL Query optimisation. I really need it soon, thanks!

Thank you man!

When you have multiple colums in where clause and sometime index will not hit. Looking forwad for a 2nd part of this video explaining best practices when there are multiple columns in where clause then what kind of and order of index should be made. Separate index on each column or combined index and how this will impact on write time? Also if we write like query as 'Zs%' will index hit?

Thank you so much !💕

Made my day !

This is excellent...

Very nice video! Thanks

Thank's man, very useful info)

Which one is better, having different indexes for different columns or having one index containing multiple columns and can you give examples of in which case which option to go for?

Like %za% is slow, however I feel, Like za% would benefit from index

Please create a video on sql joins time complexity analysis, as you have done for index scan, index only and table scan

Thanks :)

Super engaging!

good info... thanks... very interested in how: - a dattabase decide in what page save a record - how variable size rows are stored (a page can have 1 row... or 10 rows) - what if a record is bigger than the page size? keep up----

@Hussein Nasser Can you also discuss multi-column index? Thanks!

Love it, thank you :)

Will putting an index on a View help?

Very nice video

great video!

Beautiful.

thats a bloody good video mate. Whats the ram speed youre running there?

Does the execution time include the CPU time and the release memory time?

thank you!

Hello Hussein! Great video as always. This left me with some questions. How are we supposed to implement a search functionality if "like" is not a good idea? Should we create as much indexes as possible? or should we create indexes on most used fields? Thanks again.

Superb

I'd watched this video 6 months ago and understand nothing, now I watch this video and I am working on some kind of project that needs indexing Finally I Understand Thank Hussien But, wait a little bit, how do you design your youtube thumbnail

What is the best way to use instead of Like query if some similar query needs to be implemented. Use fullTextSearch or elasticSearch or any other things or is there a way?

Would be nice to know if the index helps in case the expression is "like 'Za%'". Intuitively it should be able to the rows starting with Za and take advantage of the index, what do you think?

amazing video ❤

so how can we make matching expressions fast ?, and will a document-oriented database face the same problem and search through all documents in a collection to match maybe a name or will it be faster ? (i mean do we have an option to optimize queries like this ?)