Easy database indexing strategies

[@metaphoricallyspeaking]

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

[@rembautimes8808]

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

[@FarazSamapoor]

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]

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

[@manishrc]

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!

[@fallenpentagon1579]

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

[@PlanetScale]

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

[@philadams9254]

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]

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.

[@jizhang75]

you video tutorial has been always informative, and instructive, really really appriciate

[@henrymaddocks984]

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

[@carnavalesenpanama]

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.

[@oOShaoOo]

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]

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

[@polojuninho]

Amazing video. Use cases are so relatable!

[@shutterassault1]

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

[@HenriqueFoletto]

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]

This is a great example! Adding to my list

[@kiranjoshi1077]

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

[@amaelftah]

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

[@PlanetScale]

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

[@marrtins]

Great channel for MySQL learning!

[@GameDesignerJDG]

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]

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

[@mityukov]

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 😅

[@xcrap]

Super educational and useful. Thanks :)

[@arnothar8035]

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]

You're not wrong 😉 kzbin.info/www/bejne/mmerkod-oL2kmbM

[@cassiosouza10]

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

[@fernandoacostanet]

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

[@PlanetScale]

Mission accomplished!

[@vikasvimal01]

Super insightful

[@lukeweston1234]

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

[@PlanetScale]

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

[@HJ-jg4ql]

These videos are really good.

[@leonarddurmishi6265]

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]

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.

[@yatskanych_oleksandr]

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

[@customraspi]

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

[@RaicaBogdan]

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

[@Kauto]

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

[@bradvanskyhawk7119]

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?

[@MD-mp7zm]

Amazing video, Is it possible to handle datetime ragnes based on timezone. Timezones are my nightmare these days.

[@c01d_h4nds]

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

[@Soarex]

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]

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

[@SirKenchalot]

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]

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!

[@noamanahmed1]

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]

I believe it will just be NULL in that case

[@Pekz00r]

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]

Unfortunately not! It's a one way binding

[@Pekz00r]

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

[@bensonnjonjo]

Great content, very useful as usual

[@nikolav6350]

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.

[@gebi84]

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]

Yes for sure. On my list!

[@fernandopillo]

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

[@PlanetScale]

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

[@camilosad1979]

amazing content, thank you for sharing

[@mrbigsmile3902]

MySQL uses 'unsigned tinyint' instead of boolean?

[@PlanetScale]

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

[@kirilldanshin]

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]

Ooo that's interesting

[@ryanriddell8376]

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

[@PlanetScale]

TablePlus! kzbin.info/www/bejne/bYfCdH14hb-Dm9U

[@nelson6e65]

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

[@PlanetScale]

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

[@ElPayetSUPERVAN]

This is soooo good, thank you sir !

[@TheROck3052]

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

[@marancibia1971]

Awesome video! Thanks

[@taquanminhlong]

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]

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

[@Gerbuuun]

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

[@bopuc]

Life ALTERing. Thank you.

[@PlanetScale]

😂

[@nickolayfetlistov4416]

You are the perfect!!!

[@run-dd]

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]

Still, pretty good examples right?

[@dave6012]

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]

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]

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 🙂

[@rajapanda832]

Can you please make a video about Partitioning in MySQL

[@PlanetScale]

Yes!

Nice. I’m ready to become a MySQL Monk

[@stephenpaek9175]

Thank you very much

[@BradleyBernard]

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]

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]

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

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

[@griffadev]

What is this GUI?

[@TrondHus]

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.

[@RimantasBelovas]

Loved it! 🎅

[@vishaldinesh]

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

[@PlanetScale]

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

[@blackriverID]

Superb🎉🎉🎉🎉

[@TravisFont]

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

[@TheStabacco]

educational and entertaining, keep it up!

[@PlanetScale]

🫶 Thank you so much!

[@BrunoBernard-kn6vt]

How do you populate your database for videos ?

[@PlanetScale]

I use a little Laravel app and the Faker library

[@BrunoBernard-kn6vt]

Thanks :) do you mind open source it ?

[@PlanetScale]

It's just one giant script file 🤐

[@chrishabgood8900]

Will these work in Postgres?

[@PlanetScale]

I'm honestly not sure!

[@akateet]

so cool!!! thanks

[@rrd_webmania]

Great thanks

[@filon861]

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]

You're too kind. Thank you!

[@pietraderdetective8953]

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

[@PlanetScale]

Interesting! I'll add it to my list

[@abdellahcodes]

you've missed something: kitchen sink awesome video Aaron

[@PlanetScale]

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

[@lpanebr]

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]

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]

@@PlanetScale I love your content. Thank you.

[@mdsameer-l3p]

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

[@davidmutua6971]

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_]

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]

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

[@Svish_]

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

[@FGj-xj7rd]

Adding JSON to relational database was a mistake. I have had so many dreadful queries to deal with regarding them 😂 So much unmaintainable doodoo 😭💩

[@PlanetScale]

It can get out of control quickly, that's for sure

[@nonnnth]

Did he just do an age reveal???

[@PlanetScale]

Or did I? We'll never know!