Don't hide your database indexes!
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Using a redundant condition is a technique to allow MySQL to use an index where it might otherwise be impossible!
00:00 Intro
01:06 Redundant condition drawing
02:12 Obfuscated indexes in MySQL
03:04 Obfuscated index example
04:50 Redundant condition example
06:18 Explaining the ADDTIME query
07:18 Adding a redundant condition
08:59 A second example
10:41 When to use redundant conditions
📚 Learn more about PlanetScale at planetscale.com/youtube.
------------------
💬 Follow PlanetScale on social media
• Twitter: / planetscaledata
• Discord: / discord
• TikTok: / planetscale
• Twitch: / planetscale
• LinkedIn: / planetscale - วิทยาศาสตร์และเทคโนโลยี
![[LIVE] : ONE ลุมพินี 71 วันนี้!! คู่เอก "ทรงชัยน้อย vs รักษ์"](http://i.ytimg.com/vi/_AGPnq2p6Js/mqdefault.jpg)
Wow, I'm really glad TH-cam recommended me this channel. This is really well explained, with great examples, even for people (like me) who aren't DBA experts, just devs
Very good explanation! Just a meta-note: timestamps in the description have to start at 0:00 to show in the player
Doh, thank you!
Great analogy and communicated superbly. Love it!
This is awesome!
One of the best internet teach is around again. Let's like the video, watch and learn something new today.
Glad you enjoyed it!
OMG this is the best I have seen in year
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 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.
Very good! Very good content very good explanation very good performance.
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
Brilliant
Great videos A A ron
💗💗💗
🥰
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
Would be even better to show the explain analyze for these, to show the filtering from circle b
Great stuff though!
So if I have index created on first_name and I use LOWER(first_name) for comparison in a query. Will the index created on first_name not be used??
It will
a) not be used
b) depending on your Collation (Case Insensitive?) Lower() might not even be neccessary
:-)
There's a video on that, too. "MySQL case insensitive searching (don't use LOWER)"
Great tip
👍👍👍
Do a video on books about MySQL!
That's a good idea actually... Thank you!
@@PlanetScale A beginner to expert roadmap of books would be a great Aaron Francis video I believe.
What app is used in the video?
Table Plus!
ok, that was 10/10... again..
What are "Indexes"?
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.
I won the prize! 👍 😄
Why not force index?
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.
Thanks for the idea! We've been experimenting and the longer videos do a whole lot better unfortunately. We'll keep experimenting though
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 😅