How to Read MySQL EXPLAINs
planetscale.com
planetscale.com
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.
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.
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://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.
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.
It's been around in Postgres for a long time so I was amazed when mysql 5.6 did not recognize it.