Wow, I'm really glad RU-vid recommended me this channel. This is really well explained, with great examples, even for people (like me) who aren't DBA experts, just devs
I wanted to add this on a separate comment: sometimes when running explain on a query, you will still get a table scan instead of an index scan because the table does not contain enough rows to make sense for the database to do an index scan. Some databases allow you to "force" the use of an index, but that may lead to performance degradation. Always, test whether using the index increases the query's performance.
This is good advice not just for MySql, but SQL Server also. Although, for your opening comments, don't underestimate the brain. It processes far more information than databases can, it just does it subconsciously. If we had to consciously calculate with a computer everything our brain processes, we would fry it's circuits.
Really great content, the example on 7:19 is the most interesting one. I've tried it by myself and it really works. However, does it always work? I mean, is there a chance that another database/db engine might execute 'AND' conditions in a less optimal way ignoring the fact that it's beneficial to get results for "circle B" first?
I have an even better, real-life example of this. So the goal was to determine which cab drivers were inside of 3km radius of a certain point, and the app was calculating the distance for every cab driver to that point, and there were a lot of them. So then I figured out that every cab driver was inside a square of 6x6km. Based on the latitude, I calculated the width and height of that square in degrees, and applied that to the latitude and longitude columns of the taxi drivers table
i cant create a free database in planet scale . it asks me for my card info and it wont charge for anything until and unless i choose the pro options but my card always gets declined
We actually have a whole section available in our free MySQL for Developers course! This would explain indexes best: planetscale.com/learn/courses/mysql-for-developers/indexes/introduction-to-indexes
Think of a phone catalog In a phone catalog you have a list of names along with a list of phone numbers that match said names. If you want to search in that catalog efficiently, you'd use the pagination feature. So if you want to find what's George's phone number for example, you'd go straight to G page. In a database the index in this scenario would be this pagination feature (letters A-Z that relate to contact's names). You could in theory say that this pagination is actually an index on contact name column. Obviously this is a gross oversimplification but it should help you understand why they need to exist and how much the performance increases because of their existence.
Although your content is awesome, but I am always reluctant to click on the video because of the long duration I always feel like your 10 mins videos can be encapsulated in 2 shorts... It does not matter if I am alone, but may be you should try to put up a poll or something to see if I am not alone.
I like the longer, more in-depth videos personally, as I'm new to database development. Although I could imagine agreeing and wanting to skip the fluff if I were already experienced.
Long videos from Aaron are very enjoyable. He shares his happy inner state along with the creatively and funnily wrapped content. So it is not only about tips and tricks, but much more. And I noticed, this way the knowledge also absorbs deeper 😅