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.
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.
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.
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.
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);
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.
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.
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?
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.
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.
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!
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.
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
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!)
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. 🤔
@@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?
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.
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
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
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
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 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.
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 🙂
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.
