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.
@PlanetScale11 ай бұрын
Thank you Faraz! I'm gonna lean into the practical examples for a little while, I agree with you.
@metaphoricallyspeaking11 ай бұрын
Incredible video - awesome work Aaron (and the PlanetScale team)! 👏
@rembautimes88087 ай бұрын
Yeah it’s well structured and presented with an innovative background as well
@jizhang75Ай бұрын
you video tutorial has been always informative, and instructive, really really appriciate
@manishrc10 ай бұрын
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!
@oOShaoOo11 ай бұрын
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.
@PlanetScale11 ай бұрын
Oh nice, I love it! Thanks for letting me know! You listened to my plea at the end 🥹
@henrymaddocks98410 ай бұрын
This is a great series and you're a fantastic presenter
@carnavalesenpanama10 ай бұрын
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.
@amaelftah11 ай бұрын
really nice video Aaron i liked it so much and waiting for more tips and tricks like that
@PlanetScale11 ай бұрын
Thank you! Glad you enjoyed it. It's a bit different style than our normal stuff, we'll see how it does 🤞🤞
@xcrap11 ай бұрын
Super educational and useful. Thanks :)
@polojuninho11 ай бұрын
Amazing video. Use cases are so relatable!
@kiranjoshi10772 ай бұрын
This is simply great knowledge sharing, of course for free of cost.👋
@philadams925411 ай бұрын
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?
@PlanetScale11 ай бұрын
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.
@Kauto11 ай бұрын
Great video as always. I would like to see a complex functional index example with a where and a group by.
@HenriqueFoletto11 ай бұрын
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.
@PlanetScale11 ай бұрын
This is a great example! Adding to my list
@vikasvimal0111 ай бұрын
Super insightful
@shutterassault17 ай бұрын
Very helpful. I use Oracle all day as a web developer. Need to think about indexing more
@lukeweston123411 ай бұрын
I would pay a solid $50 for a full course from you at this point!
@PlanetScale11 ай бұрын
You shall have it for free! planetscale.com/mysql. Enjoy!
@fallenpentagon157910 ай бұрын
I was thinking "wow Aaron, he's so generous, he's so kind" the entire video
@PlanetScale10 ай бұрын
Now imagine I'd only given you nine examples! You'd be furious! Close call 😮💨😮💨
@c01d_h4nds11 ай бұрын
loving the content and your way of explaining it 🔥..
@marrtins11 ай бұрын
Great channel for MySQL learning!
@fernandoacostanet10 ай бұрын
I have no idea about anything 😂 this channel makes mysql looks awesome
@PlanetScale10 ай бұрын
Mission accomplished!
@yatskanych_oleksandr9 ай бұрын
As always, very good video. And now i want to implement it into my project. Thanks you!
@stephenpaek91758 ай бұрын
Thank you very much
@nickolayfetlistov441611 ай бұрын
You are the perfect!!!
@customraspi4 ай бұрын
Can you also make a video about MemCP database which is wire compatible to MySQL and does such indexes automatically?
@bradvanskyhawk711910 ай бұрын
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?
@RaicaBogdan11 ай бұрын
awesome video! I like the functional indexes and generated columns a lot now!
@GameDesignerJDG11 ай бұрын
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.
@PlanetScale11 ай бұрын
I haven't tested your specific query but it seems correct to me, and would be a good alternative! Thanks for commenting 🤗
@mityukov11 ай бұрын
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 😅
@leonarddurmishi626511 ай бұрын
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?
@PlanetScale11 ай бұрын
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.
@blackriver202111 ай бұрын
Superb🎉🎉🎉🎉
@TheROck305211 ай бұрын
Great video. I would like to know more about JSON.
@Soarex11 ай бұрын
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?
@PlanetScale11 ай бұрын
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!)
@cassiosouza108 ай бұрын
Great content man, best so far, keep it coming.
@arnothar803511 ай бұрын
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.
@PlanetScale11 ай бұрын
You're not wrong 😉 kzbin.info/www/bejne/mmerkod-oL2kmbM
@gebi8411 ай бұрын
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?
@PlanetScale11 ай бұрын
Yes for sure. On my list!
@bopuc11 ай бұрын
Life ALTERing. Thank you.
@PlanetScale11 ай бұрын
😂
@akateet9 ай бұрын
so cool!!! thanks
@HJ-jg4ql10 ай бұрын
These videos are really good.
@rrd_webmania9 ай бұрын
Great thanks
@nikolav635010 ай бұрын
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.
@bensonnjonjo11 ай бұрын
Great content, very useful as usual
@SirKenchalot11 ай бұрын
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.
@PlanetScale11 ай бұрын
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!
@mrbigsmile390211 ай бұрын
MySQL uses 'unsigned tinyint' instead of boolean?
@PlanetScale11 ай бұрын
Yup! Boolean also works, but is an alias for unsigned tinyint
@camilosad19799 ай бұрын
amazing content, thank you for sharing
@fernandopillo11 ай бұрын
Amazing! Do you have any vídeo about Explain? Thanks!
@PlanetScale11 ай бұрын
Not yet! It's on my list though. Soon!
@noamanahmed111 ай бұрын
Awesome videos as always. One quick question. What will happen if there is no email key in JSON data for some of the columns?
@PlanetScale10 ай бұрын
I believe it will just be NULL in that case
@RimantasBelovas11 ай бұрын
Loved it! 🎅
@Gerbuuun11 ай бұрын
Can you do a video about data aggregation? e.g. json_arrayagg and if/why you should use them
@Pekz00r10 ай бұрын
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?
@PlanetScale10 ай бұрын
Unfortunately not! It's a one way binding
@Pekz00r10 ай бұрын
@@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.
@marancibia197111 ай бұрын
Awesome video! Thanks
@ryanriddell837611 ай бұрын
What app are you using to write/execute the queries?
@PlanetScale11 ай бұрын
TablePlus! kzbin.info/www/bejne/bYfCdH14hb-Dm9U
@run-dd11 ай бұрын
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
@PlanetScale11 ай бұрын
Still, pretty good examples right?
@kirilldanshin11 ай бұрын
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
@PlanetScale11 ай бұрын
Ooo that's interesting
@ElPayetSUPERVAN11 ай бұрын
This is soooo good, thank you sir !
@TravisFont10 ай бұрын
A bit long, but a lot of fire examples 🔥🔥👍
@filon86111 ай бұрын
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`
@PlanetScale11 ай бұрын
You're too kind. Thank you!
@griffadev8 ай бұрын
What is this GUI?
@TrondHus9 ай бұрын
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.
@taquanminhlong11 ай бұрын
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... ?
@PlanetScale11 ай бұрын
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
11 ай бұрын
Nice. I’m ready to become a MySQL Monk
@nelson6e6511 ай бұрын
Those generated columns do work in MySQL 5.7 same as in MySQL 8 (intelligent index)?
@PlanetScale11 ай бұрын
Generated columns work the same in MySQL 5.7 and 8! Functional indexes are an 8 only feature
@rajapanda83211 ай бұрын
Can you please make a video about Partitioning in MySQL
@PlanetScale11 ай бұрын
Yes!
@BradleyBernard10 ай бұрын
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 :)
@PlanetScale10 ай бұрын
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
@BradleyBernard10 ай бұрын
@@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!
@PlanetScale10 ай бұрын
@@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.
@dave601211 ай бұрын
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 🙂
@PlanetScale11 ай бұрын
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!
@dave601211 ай бұрын
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 🙂
@pietraderdetective895311 ай бұрын
hey great video as always! idea for a new video: what about making a CRUD app but with raw SQL? sounds fun!
@PlanetScale11 ай бұрын
Interesting! I'll add it to my list
@chrishabgood890010 ай бұрын
Will these work in Postgres?
@PlanetScale10 ай бұрын
I'm honestly not sure!
@TheStabacco11 ай бұрын
educational and entertaining, keep it up!
@PlanetScale11 ай бұрын
🫶 Thank you so much!
@vishaldinesh11 ай бұрын
Hey it's Mr dunphy from planet scale, Hi.
@PlanetScale11 ай бұрын
Hey kids, it's MySQL Dad. I believe in you.
@BrunoBernard-kn6vt11 ай бұрын
How do you populate your database for videos ?
@PlanetScale11 ай бұрын
I use a little Laravel app and the Faker library
@BrunoBernard-kn6vt11 ай бұрын
Thanks :) do you mind open source it ?
@PlanetScale11 ай бұрын
It's just one giant script file 🤐
@davidmutua69718 ай бұрын
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);
@abdellahcodes11 ай бұрын
you've missed something: kitchen sink awesome video Aaron
@PlanetScale10 ай бұрын
Haha you had me scared at first! Thank you so much
@mdsameer-l3p10 ай бұрын
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
@lpanebr11 ай бұрын
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..
@PlanetScale11 ай бұрын
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
@lpanebr11 ай бұрын
@@PlanetScale I love your content. Thank you.
@Svish_10 ай бұрын
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. 🤔
@PlanetScale10 ай бұрын
Haha you can type boolean, but it turns into a tiny int. It's an alias
@Svish_10 ай бұрын
@@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-xj7rd11 ай бұрын
Adding JSON to relational database was a mistake. I have had so many dreadful queries to deal with regarding them 😂 So much unmaintainable doodoo 😭💩
@PlanetScale11 ай бұрын
It can get out of control quickly, that's for sure