Easy database indexing strategies

Рет қаралды 24,196

9 ай бұрын

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: kzfaq.info/get/bejne/g7JiZ6qD376qaas.html
Generated hash indexes video: kzfaq.info/get/bejne/a8eVradzxrTKk5c.html
--------
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.gg/pDUGAAFEJx
• TikTok: www.tiktok.com/@planetscale
• Twitch: www.twitch.tv/planetscale
• LinkedIn: www.linkedin.com/company/planetscale

Пікірлер: 131

@outreach360-tech 8 ай бұрын

This video is literally the first time I understood how and why a database administrator can be a dedicated job

@BarakaAndrew 8 ай бұрын

Lol me too, I just learned about this today even though I always index LOWER() on postgresql. I got tables to optimize now

@FarazSamapoor 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

Thank you Faraz! I'm gonna lean into the practical examples for a little while, I agree with you.

@metaphoricallyspeaking 9 ай бұрын

Incredible video - awesome work Aaron (and the PlanetScale team)! 👏

@rembautimes8808 5 ай бұрын

Yeah it’s well structured and presented with an innovative background as well

@polojuninho 9 ай бұрын

Amazing video. Use cases are so relatable!

@henrymaddocks984 8 ай бұрын

This is a great series and you're a fantastic presenter

@manishrc 8 ай бұрын

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!

@cassiosouza10 6 ай бұрын

Great content man, best so far, keep it coming.

@carnavalesenpanama 8 ай бұрын

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.

@xcrap 9 ай бұрын

Super educational and useful. Thanks :)

@PM1_57 9 ай бұрын

loving the content and your way of explaining it 🔥..

@amaelftah 9 ай бұрын

really nice video Aaron i liked it so much and waiting for more tips and tricks like that

@PlanetScale 9 ай бұрын

Thank you! Glad you enjoyed it. It's a bit different style than our normal stuff, we'll see how it does 🤞🤞

@yatskanych_oleksandr 7 ай бұрын

As always, very good video. And now i want to implement it into my project. Thanks you!

@kiranjoshi1077 14 күн бұрын

This is simply great knowledge sharing, of course for free of cost.👋

@RaicaBogdan 9 ай бұрын

awesome video! I like the functional indexes and generated columns a lot now!

@vikasvimal01 9 ай бұрын

Super insightful

@HJ-jg4ql 8 ай бұрын

These videos are really good.

@bensonnjonjo 9 ай бұрын

Great content, very useful as usual

@marrtins 9 ай бұрын

Great channel for MySQL learning!

@marancibia1971 9 ай бұрын

Awesome video! Thanks

@camilosad1979 7 ай бұрын

amazing content, thank you for sharing

@Kauto 9 ай бұрын

Great video as always. I would like to see a complex functional index example with a where and a group by.

@oOShaoOo 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

Oh nice, I love it! Thanks for letting me know! You listened to my plea at the end 🥹

@akateet 7 ай бұрын

so cool!!! thanks

@stephenpaek9175 6 ай бұрын

Thank you very much

@ElPayetSUPERVAN 9 ай бұрын

This is soooo good, thank you sir !

@shutterassault1 5 ай бұрын

Very helpful. I use Oracle all day as a web developer. Need to think about indexing more

@RimantasBelovas 9 ай бұрын

Loved it! 🎅

@fernandoacostanet 9 ай бұрын

I have no idea about anything 😂 this channel makes mysql looks awesome

@PlanetScale 9 ай бұрын

Mission accomplished!

@nickolayfetlistov4416 9 ай бұрын

You are the perfect!!!

@rrd_webmania 7 ай бұрын

Great thanks

@philadams9254 9 ай бұрын

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?

@PlanetScale 9 ай бұрын

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.

@HenriqueFoletto 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

This is a great example! Adding to my list

@lukeweston1234 9 ай бұрын

I would pay a solid $50 for a full course from you at this point!

@PlanetScale 9 ай бұрын

You shall have it for free! planetscale.com/mysql. Enjoy!

@TheStabacco 9 ай бұрын

educational and entertaining, keep it up!

@PlanetScale 9 ай бұрын

🫶 Thank you so much!

@TheROck3052 9 ай бұрын

Great video. I would like to know more about JSON.

@bopuc 9 ай бұрын

Life ALTERing. Thank you.

@PlanetScale 9 ай бұрын

😂

@fallenpentagon1579 8 ай бұрын

I was thinking "wow Aaron, he's so generous, he's so kind" the entire video

@PlanetScale 8 ай бұрын

Now imagine I'd only given you nine examples! You'd be furious! Close call 😮‍💨😮‍💨

@blackriver2021 9 ай бұрын

Superb🎉🎉🎉🎉

@gebi84 9 ай бұрын

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?

@PlanetScale 9 ай бұрын

Yes for sure. On my list!

9 ай бұрын

Nice. I’m ready to become a MySQL Monk

@TravisFont 9 ай бұрын

A bit long, but a lot of fire examples 🔥🔥👍

@arnothar8035 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

You're not wrong 😉 kzfaq.info/get/bejne/mpx4lLd50L7Vk4U.html

@leonarddurmishi6265 9 ай бұрын

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?

@PlanetScale 9 ай бұрын

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.

@filon861 9 ай бұрын

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`

@PlanetScale 9 ай бұрын

You're too kind. Thank you!

@GameDesignerJDG 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

I haven't tested your specific query but it seems correct to me, and would be a good alternative! Thanks for commenting 🤗

@mityukov 9 ай бұрын

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 😅

@customraspi 2 ай бұрын

Can you also make a video about MemCP database which is wire compatible to MySQL and does such indexes automatically?

@Gerbuuun 9 ай бұрын

Can you do a video about data aggregation? e.g. json_arrayagg and if/why you should use them

@kirilldanshin 9 ай бұрын

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

@PlanetScale 9 ай бұрын

Ooo that's interesting

@Soarex 9 ай бұрын

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?

@PlanetScale 9 ай бұрын

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!)

@mrbigsmile3902 9 ай бұрын

MySQL uses 'unsigned tinyint' instead of boolean?

@PlanetScale 9 ай бұрын

Yup! Boolean also works, but is an alias for unsigned tinyint

@pietraderdetective8953 9 ай бұрын

hey great video as always! idea for a new video: what about making a CRUD app but with raw SQL? sounds fun!

@PlanetScale 9 ай бұрын

Interesting! I'll add it to my list

@vishaldinesh 9 ай бұрын

Hey it's Mr dunphy from planet scale, Hi.

@PlanetScale 9 ай бұрын

Hey kids, it's MySQL Dad. I believe in you.

@noamanahmed1 9 ай бұрын

Awesome videos as always. One quick question. What will happen if there is no email key in JSON data for some of the columns?

@PlanetScale 9 ай бұрын

I believe it will just be NULL in that case

@TrondHus 7 ай бұрын

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.

@SirKenchalot 9 ай бұрын

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.

@PlanetScale 9 ай бұрын

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!

@taquanminhlong 9 ай бұрын

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... ?

@PlanetScale 9 ай бұрын

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

@BradleyBernard 9 ай бұрын

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 :)

@PlanetScale 9 ай бұрын

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

@BradleyBernard 9 ай бұрын

@@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!

@PlanetScale 9 ай бұрын

@@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.

@Pekz00r 8 ай бұрын

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?

@PlanetScale 8 ай бұрын

Unfortunately not! It's a one way binding

@Pekz00r 8 ай бұрын

@@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.

@dave6012 9 ай бұрын

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 🙂

@PlanetScale 9 ай бұрын

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!

@dave6012 9 ай бұрын

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 🙂

@run-dd 9 ай бұрын

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

@PlanetScale 9 ай бұрын

Still, pretty good examples right?

@bradvanskyhawk7119 8 ай бұрын

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?

@abdellahcodes 9 ай бұрын

you've missed something: kitchen sink awesome video Aaron

@PlanetScale 9 ай бұрын

Haha you had me scared at first! Thank you so much

@fernandopillo 9 ай бұрын

Amazing! Do you have any vídeo about Explain? Thanks!

@PlanetScale 9 ай бұрын

Not yet! It's on my list though. Soon!

@nelson6e65 9 ай бұрын

Those generated columns do work in MySQL 5.7 same as in MySQL 8 (intelligent index)?

@PlanetScale 9 ай бұрын

Generated columns work the same in MySQL 5.7 and 8! Functional indexes are an 8 only feature

@nikolav6350 8 ай бұрын

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.

@ryanriddell8376 9 ай бұрын

What app are you using to write/execute the queries?

@PlanetScale 9 ай бұрын

TablePlus! kzfaq.info/get/bejne/bbyPdq1ztcC0lac.html

@griffadev 7 ай бұрын

What is this GUI?

@chrishabgood8900 8 ай бұрын

Will these work in Postgres?

@PlanetScale 8 ай бұрын

I'm honestly not sure!

@rajapanda832 9 ай бұрын

Can you please make a video about Partitioning in MySQL

@PlanetScale 9 ай бұрын

Yes!

@BrunoBernard-kn6vt 9 ай бұрын

How do you populate your database for videos ?

@PlanetScale 9 ай бұрын

I use a little Laravel app and the Faker library

@BrunoBernard-kn6vt 9 ай бұрын

Thanks :) do you mind open source it ?

@PlanetScale 9 ай бұрын

It's just one giant script file 🤐

@user-gm8xr7qr2l 8 ай бұрын

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

@lpanebr 9 ай бұрын

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..

@PlanetScale 9 ай бұрын

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

@lpanebr 9 ай бұрын

@@PlanetScale I love your content. Thank you.

@davidmutua6971 6 ай бұрын

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);

@Svish_ 9 ай бұрын

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. 🤔

@PlanetScale 9 ай бұрын

Haha you can type boolean, but it turns into a tiny int. It's an alias

@Svish_ 9 ай бұрын

@@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?