The key point of this video is that index doesn't "just" increase speed of read, it actually decreases the amount of rows SQL has to read to get you the necessary data out. This is awesome, makes sense, but I never realized it, thanks!
This sort of issue creeps up on you. When you did acceptance testing and had 1000 records, but only returned 10, the difference was negligible, but after 3 months and your records are 100K you might notice some slowing and then when you hit 500K rows suddenly you hit a tipping point where stuff breaks or is unusable. So performance tuning your DB needs to be an on-going process. Also don't overlook the benefits of good housekeeping and delete records that no longer have any business value.
@@patrick-dev PlanetScale is built on MySQL. If you're looking for a Postgres provider that's sort of in the same space there's Neon and also Supabase. I can't vouch for either.
I think this video finally got me over the bump of not understanding how table scans work. No excuse now not to dive in with explain and explain analyse. Please do more content like this where you debug and analyse slow queries
About that, I had a query which was very slow, but after changing the order of joins and moving a table near the top, it got way faster. I thought that the query optimizer will handle that and the order of tables in join isn't that important, but it is
Wish I could add indices to my brain so I don't get lagspikes when I'm doing exams. Do you think you could implement something like this for PlanetScale?
Man your videos are doing wonders than my professor reading from the slides, which i leanrt nothing from. I just wonder that after learning the basics of SQL, what path or resources should I go for? Because what I feel like is that when it comes to analyzing problems, I can't think of anything. Appreciate any feedback❤.
I really miss that 'explain' function with T-SQL. The 'explain analyze' looks to be similar to "execution plan" in T-SQL as that runs the query as well with the result being a graphical explaination of the query that ran.
Had a similar issue. In my case, it was a select random row query. It got me to 3 billion row reads . In the end, I fixed it with a mix of cache and queries, and my usage dropped a lot
Huh, I always thought that indexed colums are counted towards rows read on planetscale... I should actually use planet scale after all for our services.
/me nods in quiet agreement For more on rows read (which is where I source the info for this video!) check out this doc: planetscale.com/docs/concepts/billing#understanding-rows-read
So if I understand correctly, it's your queries that are supposed to tell you which indexes to have and not your database alone, so if you're just starting and just created a database, you should add indexes as you understand your app more and the queries that lt needs to run most frequently
Is it worth covering how sometimes you can have too many indexes? Ie I had a query get slower when I added a new index to a table, as the engine used that index instead of a better one. Obviously can be fixed by either specifying which index to use, or binning the new one. But took a while to work out why performance on a query I wasn’t working on had dropped from trying to improve another query!
How many indices are too many? I friend have a app that filter/search products for any field (name, description,price,createdAt, updatedAt) and added indices in all the fields, that makes sense?
The rule of thumb is "as many as you need, as few as you can get away with." It's really important to make sure that your compound indexes are serving as many queries as possible. For more in-depth guides into indexes, check out the videos at planetscale.com/mysql.
You would need to measure different states to get any meaningful answers. However a few nuggets to think about: - more indices can hurt insert/update/remove operations. So making more indices than your application uses may hurt, if your application has a decent ratio of writes vs reads. - If it's mostly read-only then the only drawback might be storage space, but otherwise fine. Depends on your scaling. - Also if you have queries that use a set of fixed conjunctions of columns rather than some highly dynamic queries, then you could probably have a few composite/multi-column indexes suited to your application rather than many single-column indexes. So yeah, basically any advice to be given will highly depend on your application and what it does. Read vs writes, scale/storage space, fixed/limited use cases vs queries that are all over the place selecting various combinations of columns Remember to always measure! Put together benchmarks that run your real application queries against a lot of test data! Or some sort of backup/copy of your real dataset.
I love your videos, not only because of the content, but as well the voice and video setup. By any chance could you share which Microphone, setup, how do you record your videos? Thank you for the good work for you and PlanetScale
Thank you so much! I share some behind the scenes on Twitter at twitter.com/aarondfrancis, but long story short is: Canon M50 camera, screenflow screen recorder, Shure mv7 mic!
I really love your content! Most of my infrastructure is on DigitalOcean in Singapore region. If I want to use PlanetScale I have to migrate all of my stuff to AWS to be sure the connection is fast enough between my app servers and the DB? Thanks!
You'd at least need to pick a PlanetScale region that is as close to you as possible. Putting it in AWS would probably help, but as long as you're physically close to your DB you should be ok! As always, test it in the real world tho
I will test it, but I'm pretty sure multi-datacenter setup will not work really good in my case. I have a lot of sequentuel queries in my jobs. Maybe I have to move my worker servers only. @@PlanetScale
What would've been the outcome if there were other fields in the "WHERE" clause of the query? Now, there is only "featuredAt", so indexing it solves to problem. But if there was another column involved in the WHERE? Something like "votesCount > 5"?
We would've had to look more closely at the query to probably craft the best compound index. Narrowing down to a single day does the bulk of the elimination, but there may have been an even better index available!
Hey @Planetscale, on my company, we are HEAVILY dependent on AWS RDS, how to convince my team to be flexible and migrate one or two of our MOST CRITICAL application?
@@SentinelaCosmica We have several customers that moved over from RDS and are incredibly happy. Better developer experience, near infinite scale with horizontal sharding, and pricing is usually similar or better. Migration from RDS to PlanetScale does not involve any downtime either. Happy to send you more info or set up a call with your manager if you'd like! Email me holly@planetscale.com.
@@PlanetScale certainly, i liked the vid immediately for that (not what i normally do), and the content itself did not disappoint either. you seem like a cool and sweet guy, keep doing you, be and stay happy !
Could you do a vid on optimization techniques for using a guid4/nanoid as the id for a user in place of an int? Should one keep an int as the primary key or use the nanoid as the primary key?
Index are good for reading, but don't forget that you need to rebuild all index each time you update a table. You can't just place index everywhere and hope for the best :)
If you work in PHP you could turn on mysqli_report(MYSQLI_REPORT_INDEX), and it will throw an Exception if you ever dare to query the db without or even with an bad index pretty sure other languages have similar features. (annoyingly it even throws an Exception when a table scan would be actually cheaper (e.g. for a settings table with one row) but well better than nothing)
Why use SQL (even through planetscale) when NoSQL scales infinitely without sharding. "Newsql" databases let's you get a SQL interface to a nosql database, there's even ones that are compatible with mysql I think. I wonder what's the benefit of traditional SQL like planet scale probably uses, maybe it is faster for certain use cases
Even experienced folks have plant moments from time to time. Who amongst us has not forgotten an index? Here's another good one: ruclips.net/video/k7p8OEoU-go/видео.html
@@PlanetScale hahahaha no not an actual plant, that's a good one tho - i might use it. More it being a fake scenario to show off some features. Someone saying their saas failed and they will have to shut down over a database index is wild.
TBH almost every database video could be "tl;dr use indexing." I'm always trying to explain the why behind things! Hope you'll enjoy the next ones more 🤗
Planetscale has no business having a RUclips channel this good
thank you 💅
aaaaaaaaanditsgone.mp4
The key point of this video is that index doesn't "just" increase speed of read, it actually decreases the amount of rows SQL has to read to get you the necessary data out. This is awesome, makes sense, but I never realized it, thanks!
It increases the speed by reducing the number of rows reaf
Yup, exactly! That's why I thought this was such a good example. Glad it was helpful :D
This is optimization 101. Usually to make code faster, you write less of it.
Compiler optimizations are basically just doing the same thing with less code.
7:40
This was fantastic. Can’t believe the free tier has a billion reads as well as one on one remote support.
If I was able to log off Twitter, things would be different. But until then, here I am 🫡
What a beautiful way to explain table debugging :)
I love this format of solving a real world problem and seeing the actual results. Awesome work!
9:09 It's almost always an index, sometimes it's missing, sometimes it's unneeded and sometimes it's incorrectly combined.
Thanks Aaron!
I love how you troubleshoot queries and schemas and I love that you enjoy it in a visible way 😄
Haha I do love it 😂 That's my entire content strategy, btw. Do things I'm excited about ... and be excited about them
@@PlanetScale seems like it's working!
really love this real case study and with the solutions as well
This sort of issue creeps up on you. When you did acceptance testing and had 1000 records, but only returned 10, the difference was negligible, but after 3 months and your records are 100K you might notice some slowing and then when you hit 500K rows suddenly you hit a tipping point where stuff breaks or is unusable. So performance tuning your DB needs to be an on-going process. Also don't overlook the benefits of good housekeeping and delete records that no longer have any business value.
Yup, totally! It's not a problem until it is 😬
I love watching till end and when he finally says see ya it feels like a treat to my ears. Love how he says that 😭❤
This channel is so valuable. @PlanetScale, are there any MySQL courses you would recommend that would get close to the quality of this education?
they have a free course on mysql that is taught by Aaron which is awesome. you should check the first link in the description
how about a free course taught by the same guy whom you just watched xD .. it's already there.
Haha @PM1_57 is right! 8 hours of me (if you can stand it) at planetscale.com/mysql.
@@PlanetScale Nothing PostgreSQL?
@@patrick-dev PlanetScale is built on MySQL. If you're looking for a Postgres provider that's sort of in the same space there's Neon and also Supabase. I can't vouch for either.
This channel has to be one of the best way to market a product I've ever seen (for developers)
And don't forget, shout-outs to Holly! Aaron is out here saving customer money 💰
Yeah for real! Adding this to my performance review packet 🧠
I think this video finally got me over the bump of not understanding how table scans work. No excuse now not to dive in with explain and explain analyse.
Please do more content like this where you debug and analyse slow queries
What an amazing ad for PlanetScale
Pretty cool technical explanation in the dark coating of never having free time and working on the weekend, all day, every day.
Este canal está siendo uno de los más informativos para mi en este momento. This channel is being one of the most useful for me this days.
🫶 love to hear that.
the way he explained this actually made me to subscribe
this is fun for real. never think solving db problem could be fun. subs right here
Thank you for the tips. Managed to optimize a query execution time from 30sec to just 1 sec at the company I work at.
About that, I had a query which was very slow, but after changing the order of joins and moving a table near the top, it got way faster. I thought that the query optimizer will handle that and the order of tables in join isn't that important, but it is
🤯 woah, that rules. That's why we make these videos!
That's was really interesting
You're explanations are super helpful. I leaner a lot. Thanks 🙏
I thought this would be just a advertisement video for planetscale BUT I WAS WRONG.
Absoloutly great video. Thatks for helping out the community!
🫡 happy to help
Please continue this wonderful work. Make more videos.
As always, good content. Thank you for sharing
Very insightful, thanks for sharing!
Wish I could add indices to my brain so I don't get lagspikes when I'm doing exams. Do you think you could implement something like this for PlanetScale?
I'll bring it up to management and report back
Always amazing explanations!
Fantastic explanation, thank you!
cool! I'll share this video with my friend.
Thank you so much!
Thank you. Now I know what I can do with insights😄
Thanks Cap' 🛡️ Very instructive! I should look for these metrics more often... 🧐
🫡🛡️
The Simon Sinek of SQL. Its a pleasure watching your videos.
Beautifully explained ❤
Man your videos are doing wonders than my professor reading from the slides, which i leanrt nothing from. I just wonder that after learning the basics of SQL, what path or resources should I go for? Because what I feel like is that when it comes to analyzing problems, I can't think of anything. Appreciate any feedback❤.
Based on your previous videos, I immediately thought about indexes and boi I am right as well.
Yesssss it's working!
Aaron sincere posting always brings me joy
🫶 sincere posting is the only way
Excellent video
This was brilliant thanks
I really miss that 'explain' function with T-SQL. The 'explain analyze' looks to be similar to "execution plan" in T-SQL as that runs the query as well with the result being a graphical explaination of the query that ran.
Had a similar issue. In my case, it was a select random row query. It got me to 3 billion row reads
. In the end, I fixed it with a mix of cache and queries, and my usage dropped a lot
Woah that's a wild one. Working on a video for selecting random rows right now!
Another great video! Wow!
Ohh man this is awesome
thank you so much
Huh, I always thought that indexed colums are counted towards rows read on planetscale... I should actually use planet scale after all for our services.
/me nods in quiet agreement
For more on rows read (which is where I source the info for this video!) check out this doc: planetscale.com/docs/concepts/billing#understanding-rows-read
@@PlanetScale thanks for the link!
expecting more of these kind of videos
So if I understand correctly, it's your queries that are supposed to tell you which indexes to have and not your database alone, so if you're just starting and just created a database, you should add indexes as you understand your app more and the queries that lt needs to run most frequently
I just figured it's TablePlus from a recent short
Damn, never knew this side-effect of indexes.
I have no need for your services at all, but I gotta say it is a great premise to debug on public space like you guys did.
Is it worth covering how sometimes you can have too many indexes?
Ie I had a query get slower when I added a new index to a table, as the engine used that index instead of a better one. Obviously can be fixed by either specifying which index to use, or binning the new one. But took a while to work out why performance on a query I wasn’t working on had dropped from trying to improve another query!
THIS is how u build brand loyalty
❤️
Could you explain why the index helped? How does it know to stop scanning rows?
What software do you use run sql queries? Like in 5:17
And boom - just like that, customer for life. Well done. :)
Nice!!
You make great content, thank you
Thank you for saying so!
How many indices are too many?
I friend have a app that filter/search products for any field (name, description,price,createdAt, updatedAt) and added indices in all the fields, that makes sense?
The rule of thumb is "as many as you need, as few as you can get away with." It's really important to make sure that your compound indexes are serving as many queries as possible. For more in-depth guides into indexes, check out the videos at planetscale.com/mysql.
You would need to measure different states to get any meaningful answers. However a few nuggets to think about:
- more indices can hurt insert/update/remove operations. So making more indices than your application uses may hurt, if your application has a decent ratio of writes vs reads.
- If it's mostly read-only then the only drawback might be storage space, but otherwise fine. Depends on your scaling.
- Also if you have queries that use a set of fixed conjunctions of columns rather than some highly dynamic queries, then you could probably have a few composite/multi-column indexes suited to your application rather than many single-column indexes.
So yeah, basically any advice to be given will highly depend on your application and what it does. Read vs writes, scale/storage space, fixed/limited use cases vs queries that are all over the place selecting various combinations of columns
Remember to always measure! Put together benchmarks that run your real application queries against a lot of test data! Or some sort of backup/copy of your real dataset.
Ah, just noticed he beat me to first reply 😂
Loved the video @PlanetScale
Amazing response @mme725
Does having index double the size of the table?
Come to South Africa already, we have AWS presence for a long time now
We're constantly evaluating new regions, but we don't have any concrete plans for South Africa at the moment
holy cow that was good.
what is the application that you are using to query and connect to the mysql database of planetscale?
TablePlus
TablePlus
TablePlus 😉 ruclips.net/video/7V_CJBPZPes/видео.html
This is a fantastic viceo
I’m so excited I made a typo
thanc you! always good to see you 🫡
I love your videos, not only because of the content, but as well the voice and video setup.
By any chance could you share which Microphone, setup, how do you record your videos?
Thank you for the good work for you and PlanetScale
Thank you so much! I share some behind the scenes on Twitter at twitter.com/aarondfrancis, but long story short is: Canon M50 camera, screenflow screen recorder, Shure mv7 mic!
Aaron Francis and Planetscale are a match made in heaven.
Saving the world, one query at a time 🤝
What is the name of the man talking in the video? Thank you!
i fucking love this dude!
🫡 just doin my job! 🫶
Blimey!
I really love your content! Most of my infrastructure is on DigitalOcean in Singapore region. If I want to use PlanetScale I have to migrate all of my stuff to AWS to be sure the connection is fast enough between my app servers and the DB? Thanks!
You'd at least need to pick a PlanetScale region that is as close to you as possible. Putting it in AWS would probably help, but as long as you're physically close to your DB you should be ok! As always, test it in the real world tho
I will test it, but I'm pretty sure multi-datacenter setup will not work really good in my case. I have a lot of sequentuel queries in my jobs. Maybe I have to move my worker servers only. @@PlanetScale
Graet video!
What would've been the outcome if there were other fields in the "WHERE" clause of the query? Now, there is only "featuredAt", so indexing it solves to problem. But if there was another column involved in the WHERE? Something like "votesCount > 5"?
We would've had to look more closely at the query to probably craft the best compound index. Narrowing down to a single day does the bulk of the elimination, but there may have been an even better index available!
awesome video
Did I just stumble across the Brent Ozar of planetscale?
How you go and build product without understanding this ... I checked their site and it is lagging for me...
Forgetting an index is a pretty normal thing to do! We've all done it
How do I get your glasses :)
Such a good man. Nice catch Aaron! 🔥
Hey @Planetscale, on my company, we are HEAVILY dependent on AWS RDS, how to convince my team to be flexible and migrate one or two of our MOST CRITICAL application?
@@SentinelaCosmica We have several customers that moved over from RDS and are incredibly happy. Better developer experience, near infinite scale with horizontal sharding, and pricing is usually similar or better. Migration from RDS to PlanetScale does not involve any downtime either. Happy to send you more info or set up a call with your manager if you'd like! Email me holly@planetscale.com.
@@PlanetScale Perfect, I will send you a message, so you can schedule a meeting with our manager.
@@PlanetScale I have sent you an email
that's one heck of a thumbnail
In a good way right
@@PlanetScale certainly, i liked the vid immediately for that (not what i normally do), and the content itself did not disappoint either. you seem like a cool and sweet guy, keep doing you, be and stay happy !
You're incredibly kind to say so. That means a lot to me
Could you do a vid on optimization techniques for using a guid4/nanoid as the id for a user in place of an int? Should one keep an int as the primary key or use the nanoid as the primary key?
You are IN LUCK! Researcher Brian has been digging deeeeep on this: twitter.com/brianmmdev/status/1725523027197153710. Video coming soon
Nice thumbnail, captain database!
🫡 at your service
I should add more indexes :D
Index are good for reading, but don't forget that you need to rebuild all index each time you update a table.
You can't just place index everywhere and hope for the best :)
Probably! As many as you need, as few as you can get away with.
u r a cool dude
I would like to see a video on how to handle IoT data in PlanetScale/MySQL
I've not done IoT before, is it primarily huge amounts of time series data? Is that the trouble? Would love to know more
very nice. would love some details on the cost value. the classic missing index
In this case he was able to stay on the free tier after we added the index!
@@PlanetScale i mean in the explain analyze we see a cost value for the query
Ohhh oh, my bad. Yes, working on an explain + explain analyze video!
Why not do it automatically for your users? Or at the very least suggest it to your users. Maybe acquire otter tune?
If you work in PHP you could turn on mysqli_report(MYSQLI_REPORT_INDEX), and it will throw an Exception if you ever dare to query the db without or even with an bad index pretty sure other languages have similar features. (annoyingly it even throws an Exception when a table scan would be actually cheaper (e.g. for a settings table with one row) but well better than nothing)
aaron is hero
friendly neighborhood database hero 🦸♂️
Does anyone know what SQL client is Aaron using?
Nevermind, he says it in the video 😅 (It's TablePlus)
Nobody tell em!
You have to watch this video: ruclips.net/video/7V_CJBPZPes/видео.html.
Gotta pump up those views 📈
@@PlanetScale the punishment fits the crime I'd say 😇
CFBR
11B row reads....yowza
Lotta rows!
Please don't tell my girlfriend that I'm claiming my love for relational databases once again. Thanks Planetscale ❤
That Twitter thing definitely sounds like a problem Aaron.
I could totally stop if I wanted
@@PlanetScale now I'm really worried 🤣
it bothers me when people wear glasses without lenses in them lol
besides that, how'd you like the video?
Why use SQL (even through planetscale) when NoSQL scales infinitely without sharding. "Newsql" databases let's you get a SQL interface to a nosql database, there's even ones that are compatible with mysql I think. I wonder what's the benefit of traditional SQL like planet scale probably uses, maybe it is faster for certain use cases
It’s almost always an index…but it’s always DNS!
That tweeter is a plant. There is no way a sane person would have made it that far without knowing what a db index is lmao
Even experienced folks have plant moments from time to time. Who amongst us has not forgotten an index? Here's another good one: ruclips.net/video/k7p8OEoU-go/видео.html
Wait did you mean plant like 🪴 or plant like we faked it. I assumed you were calling them a 🪴 which was a nice way to insult someone 😂
@@PlanetScale hahahaha no not an actual plant, that's a good one tho - i might use it. More it being a fake scenario to show off some features. Someone saying their saas failed and they will have to shut down over a database index is wild.
Ok I guess the only 🪴 is me, for thinking plant was some cool slang. Total plant move, honestly.
It’s almost always an index.
Almost always! Even when it's not, it probably is
TL;DR use indexing
come on, this didn’t need 10 minutes…
TBH almost every database video could be "tl;dr use indexing." I'm always trying to explain the why behind things! Hope you'll enjoy the next ones more 🤗
@@PlanetScale
I learnt something new - now Im wondering if i could plot rows read per day as part of some site performance metrics
SaaS me daddy or somethin'.. hmmm
sir this is a wendys