How to Read MySQL EXPLAINs(planetscale.com)
planetscale.com
How to Read MySQL EXPLAINs
https://planetscale.com/blog/how-read-mysql-explains
26 comments
> DBAs were very anally-retentive in those days.
They all were, famously so. It attracts a certain type which I like to call tech-bureaucrat. It is a problem of incentives: if something goes wrong with the db, they get the blame. If something simply sucks, they don't get blame for that. This is the same with all these institutionalised gate-keepers, be it "quality", "security" or whatever. I'm not sure there is a general solution.
They all were, famously so. It attracts a certain type which I like to call tech-bureaucrat. It is a problem of incentives: if something goes wrong with the db, they get the blame. If something simply sucks, they don't get blame for that. This is the same with all these institutionalised gate-keepers, be it "quality", "security" or whatever. I'm not sure there is a general solution.
There may have been write latency SLAs on the table that they were concerned about. Or maybe they had to wait until a maintenance window to release it.
I know the blog post has MySQL in the title, but one point I'll add for those of us working with Postgres: writing it as `EXPLAIN (ANALYZE, BUFFERS)` adds buffer hits to each line of the output. I've found that valuable to reveal what parts of a query are hitting the hardware the hardest and there's seemingly no reason to not add buffers to the output each time.
I didn't know about this. That's really good advice for running EXPLAIN plans on Postgres. Thanks!
Ah, how long's that been there? That would've been very helpful in a previous job.
What's the best resource for the equivalent of this page but for Postgres?
As a tool to read the output:
https://explain.depesz.com/
Not entirely necessary when you're more familiar with it, but still useful. And when starting it's much easier to read.
https://www.depesz.com/tag/unexplainable/
This series of blog posts is one option. But my impression is that most of this comes simply with experience. At first the main thing you'd look for is simply whether it uses the indexes you want it to use, or if it does full table scans.
I'd also recommend to simply read the Postgres manual, at least the parts that are not entirely about the syntax of various commands. There is a lot of information in there that will help to understand how Postgres works.
https://explain.depesz.com/
Not entirely necessary when you're more familiar with it, but still useful. And when starting it's much easier to read.
https://www.depesz.com/tag/unexplainable/
This series of blog posts is one option. But my impression is that most of this comes simply with experience. At first the main thing you'd look for is simply whether it uses the indexes you want it to use, or if it does full table scans.
I'd also recommend to simply read the Postgres manual, at least the parts that are not entirely about the syntax of various commands. There is a lot of information in there that will help to understand how Postgres works.
I personally prefer https://explain.dalibo.com over depesz's version, both from a visualization standpoint (I can more clearly see the hierarchy of operations) but also because I've had some not great experiences with depesz the individual. Great blog, but a really soured opinion of him.
Freshly translated (no affiliation whatsoever) https://postgrespro.com/community/books/internals
[deleted]
qsort(1)
Markus Winand's "Use The Index, Luke" site is the classic reference. It covers most of the major DBs, but also gets into PG specifics.
https://use-the-index-luke.com/sql/explain-plan/postgresql/o...
Understanding query plans is a universal skill that mostly transcends database particulars, so even this Planetscale article will serve as a good primer too.
https://use-the-index-luke.com/sql/explain-plan/postgresql/o...
Understanding query plans is a universal skill that mostly transcends database particulars, so even this Planetscale article will serve as a good primer too.
Has anyone used PlanetScale's SaaS offering? How has been your experience?
No PITR
Anyone tried ChatGPT and Co yet to explain some EXPlAIN output and ask it for optimization suggestions?
I'm a DBA.
I found that in general, it was OK for pretty obvious things but sometimes it missed stuff such as: running a LIKE with wild card at the start, ie: "LIKE '%Luke%'". Running that wildcard in the start tends to make the optimizer do a full scan.
Otherwise, it did okay. Just ensure you give ChatGPT the schema as well for its best suggestion.
I found that in general, it was OK for pretty obvious things but sometimes it missed stuff such as: running a LIKE with wild card at the start, ie: "LIKE '%Luke%'". Running that wildcard in the start tends to make the optimizer do a full scan.
Otherwise, it did okay. Just ensure you give ChatGPT the schema as well for its best suggestion.
This, exactly this, is what worries me about this industry.
You’re worried about people being able to get better ideas on how to optimize their queries?
thanks, first time seeing "EXPLAIN ANALYZE"
I think this is a fairly recent addition to Mysql. 8 something IIRC.
It's been around in Postgres for a long time so I was amazed when mysql 5.6 did not recognize it.
It's been around in Postgres for a long time so I was amazed when mysql 5.6 did not recognize it.
waynesonfire(1)
There was a query being emitted by the anti-fraud software of a certain well-known US bank, that was taking 45 minutes to run, which didn't please the fraud team. This fraud team weren't highly-paid elites; they were drudge workers, as much at risk of burnout as anyone.
Anyway, I was tasked with fixing this (low-importance) problem. I managed to figure out what the problem was with the query, with the help of EXPLAIN. I reconstructed the query so it ran in less than 10 seconds, and submitted the patch.
It took two months for the DBAs to let me add the requisite index to production. They'd given me a copy of the production database, eventually, so I could test my change, and make sure it didn't break anything else; but they still held it back. DBAs were very anally-retentive in those days.
I got no credit for that (except from myself :-). I'm happy to pat myself on the back for reducing the query time from absurd to reasonable, and by a factor of 1/270.
I've never used Oracle since, by the way; it's always looked to me like a blind alley. But $FINCORP used to like Oracle, and the Oracle DBAs were treated like ghodz.