Easy database indexing strategies
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Functional indexes and generated columns are a way to index things that aren't traditionally indexable, like JSON columns, data normalizations, or calculations.
To learn more about PlanetScale, head to planetscale.com/youtube!
Case insensitive searching video: • MySQL case insensitive...
Generated hash indexes video: • Faster database indexe...
--------
00:00 Functional indexes and generated columns
00:45 What is a functional index?
02:11 Example 1: Indexing the end of a string
05:05 Example 2: Case sensitive indexing
07:37 Example 3: Indexing a TEXT or BLOB
09:54 Example 4: Indexing a date part
12:57 Example 4.5: Indexing a date part, semantically
16:25 Example 5: Grouping by functional index
19:08 Example 6: Calculating age
21:09 Example 7: Data normalization
24:04 Example 8: Indexing a raw calculation
26:23 Example 9: Combining multiple statuses, and composite indexes
31:02 Example 10: Indexing JSON parts
--------
💬 Follow PlanetScale on social media
• Twitter: / planetscale
• Discord: / discord
• TikTok: / planetscale
• Twitch: / planetscale
• LinkedIn: / planetscale - วิทยาศาสตร์และเทคโนโลยี
This video is literally the first time I understood how and why a database administrator can be a dedicated job
Lol me too, I just learned about this today even though I always index LOWER() on postgresql. I got tables to optimize now
Great video! Thank you, Aaron! I really liked the practical, real-world examples that you used. It just makes the whole learning process much easier to remember and put to use.
Thank you Faraz! I'm gonna lean into the practical examples for a little while, I agree with you.
Incredible video - awesome work Aaron (and the PlanetScale team)! 👏
Yeah it’s well structured and presented with an innovative background as well
I've learn some many practical things I can use right away in just 3 of your videos. Simply excellent content and production quality. thank you!
35:21 I've used the date part indexing on a updated_at column (current timestamp) that was created by an ORM. I've had the need to find all records that were modified on day-1.
Oh nice, I love it! Thanks for letting me know! You listened to my plea at the end 🥹
This is a great series and you're a fantastic presenter
Amazing video. Use cases are so relatable!
Super educational and useful. Thanks :)
Im using functional index now on my project using case-when statement inside, and works pretty good to generate my is_verified column. Thank you Aaron.
really nice video Aaron i liked it so much and waiting for more tips and tricks like that
Thank you! Glad you enjoyed it. It's a bit different style than our normal stuff, we'll see how it does 🤞🤞
loving the content and your way of explaining it 🔥..
Great content man, best so far, keep it coming.
These videos are really good.
Very helpful. I use Oracle all day as a web developer. Need to think about indexing more
This is simply great knowledge sharing, of course for free of cost.👋
Super insightful
Great video as always. I would like to see a complex functional index example with a where and a group by.
amazing content, thank you for sharing
awesome video! I like the functional indexes and generated columns a lot now!
Great content, very useful as usual
9:00 - Those concerned about collisions should know that it's still extremely rare in md5.
You could use SHA2, which hasn't been shown to be broken, unless that's slower or not applicable somehow?
You're correct! You could use any hashing algorithm you like. For just computing checksums I like to use MD5 cause it's so fast, but you're right that it is broken for cryptographic use cases.
Awesome video! Thanks
As always, very good video. And now i want to implement it into my project. Thanks you!
Great channel for MySQL learning!
Thank you very much
so cool!!! thanks
Thank you Aaron and PlanetScale, super helpful and a joy to watch, as always!
If a may ask a question, or a suggestion for a video: How to index (or improve performance) of a query that relies on data from a different table?
Let’s say that I have a customers and an orders table, and I’d like to select all customers ordered by the sum of their orders amount, but only the orders placed in the current year, excluding refunded orders?
A generated columns in the customers table isn’t possible since it’s not possible to access columns from other tables.
I feel like this could be a common use-case (top customers of the year), but a tricky one to make performant.
This is a great example! Adding to my list
Great thanks
Again, great video. I'm learning a lot from you! Can you please make a video, how to read the explain of a slow query?
Yes for sure. On my list!
Loved it! 🎅
You are the perfect!!!
This is soooo good, thank you sir !
I would pay a solid $50 for a full course from you at this point!
You shall have it for free! planetscale.com/mysql. Enjoy!
Life ALTERing. Thank you.
😂
For example 6, you could do the index on birthday and do 'SELECT ... WHERE birthday >= now() - 46 - 1 AND birthday < now() - 46;' (if you're looking for age equality for age 46, for example) (Haven't tested this, so I might be wrong about where to put the ±1). That'd be a good explanation of a better approach, I think.
I haven't tested your specific query but it seems correct to me, and would be a good alternative! Thanks for commenting 🤗
It would be kind to allow some indeterministic indexes, though. Say, if I don't mind to regenerate it once a day at low load time 😅
Would have been cool to discuss whether you can store the result of window function (PARTITION BY) in a generated column then index it. Say I want the most recent occurrence (max date) within some group condition, can I index the window function or do I need a materialized view?
Unfortunately that wouldn't work. The window would require looking at multiple rows and a generated column can only look at the row it belongs to (as far as I know!)
Hello and very nice video as always, just a question about the is_weekend case, I don't think adding an index to 0 1 column is good as it has very low cardinality, adding an index there would possibly make the query slower, or maybe mysql would ignore it and not use it at all, any thoughts?
Great point! It would depend on the shape of your data for sure. And it might even be the case the the optimizer would ignore it for `is_weekend = 0` and use it for `is_weekend = 1`. You could also use it as part of a compound index, like the "Combining multiple statuses" example.
I have no idea about anything 😂 this channel makes mysql looks awesome
Mission accomplished!
Great video. I would like to know more about JSON.
educational and entertaining, keep it up!
🫶 Thank you so much!
Superb🎉🎉🎉🎉
I was thinking "wow Aaron, he's so generous, he's so kind" the entire video
Now imagine I'd only given you nine examples! You'd be furious! Close call 😮💨😮💨
18:16 Be careful. Datatype "timestamp" has a risk of having a overflow on dates after 2038-01-19 03:14:07 UTC, which is in kinda near future. I would suggest to use "datetime" datatype instead.
You're not wrong 😉 th-cam.com/video/d6HaTHkXqcQ/w-d-xo.html
Awesome videos as always. One quick question. What will happen if there is no email key in JSON data for some of the columns?
I believe it will just be NULL in that case
Very good video! I consider myself very knowledgable in SQL, but I learned several things here. Especially the thing with the calculations inside the composite index was really cool.
Is there a way to make a two way bind with generated function? For example in the last example, is it possible that if you change the email column, the email in the JSON field is also updated automatically?
Unfortunately not! It's a one way binding
@@PlanetScale Thank you for the answer! That is what I thought. I have to continue to do this in the application layer then. Laravel makes this pretty easy to do in either observers or mutators.
Indexing customer-provided metadata stored as json alongside the main row data
Not that often of a use case, but still good to know if you are building a SaaS or something
Ooo that's interesting
Awesome video, really like the is visible case! 🎉
Where do we draw the line of generated column vs app biz logic? IE: handling a composite flag column via app code vs in DB
My guess is it depends, but curious if you have any great examples :)
It depends! Haha sorry.
It really does though! I think the question about handling a composite flag at the app layer vs DB layer is easier, because you can't index it at the app layer. If you have an "is_visible" flag that's just a Laravel scope at the app layer, you still end up with 4 or 5 conditions at the DB layer, and that's tough to index. If you push that to the DB you can index it more easily. If that's like a default scope that you use all the time, it might make sense to push that down to the DB layer. Hope that helps! Nice to see you here
@@PlanetScale Haha yes, thanks for the explanation - I should have clarified more: for this case of the composite flags, having it as a generated column where value is handled by DB (then slap an index on it) or having it as a normal column and my Laravel app code updates its value (combining date nullability and flag yes no into one value) and then slap an index on it. Should be identical but the q is: who should do the update logic and why? Or do you think it doesn’t realllllly matter in a case like this? I like letting the DB do the work but haven’t given generated columns a chance yet but now I probably should cuz why not!
@@BradleyBernard Ah I see! If it's simple enough (doesnt require much logic) then I prefer generated column because it is impossible for it to get out of sync. If you use something like a model observer in Laravel, you have to be careful that every single update goes through that observer, otherwise you're in trouble.
Hey Aaron, great video. What about filter searching?
Say I have a stats table of different roles on a team across a dashboard of projects. I want to be able to do a partial (bonus for fuzzy) match on several column values.
This table can and will be updated often because it’s aggregated from other tables. What would be a good indexing strategy? Sounds like a nice example of generated column + functional index, but I’m not sure 🙂
Hmmm sounds like a partial (or fuzzy) match on several columns is a great use case for a FULL TEXT index, which allows for that. You might give that a go!
Thanks! I’ll look into that. Of course, if you wanted to make a video on the subject, I always enjoy how simply and eloquently you explain these concepts with examples 🙂
Can you do a video about data aggregation? e.g. json_arrayagg and if/why you should use them
Can you also make a video about MemCP database which is wire compatible to MySQL and does such indexes automatically?
How about when you use encrypted values in a table? Can/should you index an encrypted column? By encrypted, I mean encrypted by the api or the app, not encrypted at rest under the hood.
If you encrypt at the app layer and store it in the DB encrypted, you could totally index that. I'm not sure you'd even need a functional index at that point!
Amazing! Do you have any vídeo about Explain? Thanks!
Not yet! It's on my list though. Soon!
This is interesting...
I am trying to make a query I have faster. Without any search parameters, the result is returned in 4,4 seconds (which is to long). And I bet it has something to do with the query joining just 28 tables. The biggest table is just 13k big.
I also have some GROUP_CONCAT in the Select part (removed sub queries)...
So small data...
So I am watching any video on indexing with great interest.
Hey, do generated columns or functional indexes impact performance compare to static columns (normal one) when it comes to querying 100000 rows for analytics and report, etc... ?
It depends! If you write the generated column to disk it acts as a normal column. If it's virtual, then it's just like a macro for the underlying calculation. If you're going to be filtering against it, I'd probably put an index on it or make it a stored generated column
Those generated columns do work in MySQL 5.7 same as in MySQL 8 (intelligent index)?
Generated columns work the same in MySQL 5.7 and 8! Functional indexes are an 8 only feature
A bit long, but a lot of fire examples 🔥🔥👍
hey great video as always!
idea for a new video: what about making a CRUD app but with raw SQL?
sounds fun!
Interesting! I'll add it to my list
What app are you using to write/execute the queries?
TablePlus! th-cam.com/video/7V_CJBPZPes/w-d-xo.html
Question on example 9, when you added the (views > 9000). I don't think this part of the index was used in your explain plan. I think only the is_visible is used. I think you will see an attached_condition in the json formatted explain plan with views > 9000. Do you know why this is?
OK - just to contextualize - Postgres has functional index for at least 20 years (quick search -> V7.2 from 2002-02-04) - so nice to learn about, but it's not new, just said because you introduce with words like in case your MySQL hasn't that feature... If that's the case maybe MySQL is the wrong DB
Still, pretty good examples right?
Nice. I’m ready to become a MySQL Monk
MySQL uses 'unsigned tinyint' instead of boolean?
Yup! Boolean also works, but is an alias for unsigned tinyint
Dear Aaron,
I recently watched your indexing video, but I'm still grappling with a problem. In my posts table, there's a 'likes_count' column that tracks the number of likes for each post. This count changes constantly as users like posts. However, I'm unsure whether creating an index on the 'likes_count' column is good idea, considering its frequent updates. When attempting to retrieve the most current popular posts orderby likes_count, I'm uncertain about the best approach. Any insights on this would be immensely helpful.
i fucking love how u explain things, not a single boring moment. i learned a lot. thanks `select * from people where name = 'Aaron' and is_cool = 1`
You're too kind. Thank you!
Will these work in Postgres?
I'm honestly not sure!
Can you please make a video about Partitioning in MySQL
Yes!
Hey it's Mr dunphy from planet scale, Hi.
Hey kids, it's MySQL Dad. I believe in you.
How do you populate your database for videos ?
I use a little Laravel app and the Faker library
Thanks :) do you mind open source it ?
It's just one giant script file 🤐
What is this GUI?
you've missed something: kitchen sink
awesome video Aaron
Haha you had me scared at first! Thank you so much
It's all good but the DBA has to explain to the devs so that they write queries using the substring, the md5 etc... Then the devs need to do that..
Depends on the direction I suppose... if the devs are already doing it and the DBA is trying to index it, you're free! Otherwise, a generated column might be the easiest way to communicate across teams, since it has a nice neat name
@@PlanetScale I love your content. Thank you.
I am neither a DBA nor i Work with mysql.
Still, i watched this video until the end, and i feel like i am missing out something
MySQL 8.x supports functional indexes but MySQL 5.7 doesn't. Also mariadb doesn't. Am on mariadb instead you use a generated column. Then add a normal index there.
ALTER TABLE people ADD COLUMN month_created INT GENERATED ALWAYS AS (MONTH(created_at)) VIRTUAL;
ALTER TABLE people ADD INDEX idx_month_created (month_created);
Why in the world doesn't MySQL have a proper type for `boolean` values? Seems so weird and awkward to see `TINYINT` being used for a true/false type thing. 🤔
Haha you can type boolean, but it turns into a tiny int. It's an alias
@@PlanetScaleYeah, seems a bit weird to me. Maybe you can do a video on booleans in sql, if you can find some history on why there's no proper booleans in mysql?
Did he just do an age reveal???
Or did I? We'll never know!
Adding JSON to relational database was a mistake. I have had so many dreadful queries to deal with regarding them 😂
So much unmaintainable doodoo 😭💩
It can get out of control quickly, that's for sure