One way of finding slow SQL queries is to use the built-in EXPLAIN function, but it's default output is not easy to read. Here's how to export it as a more readable JSON output.
Debugging SQL queries is one of the toughest things I know, since it's so much trial and error. The error messages never seem to give you enough information, and when a query is slow there's often a lot of work to find out just where in the query the bottleneck is.
This is where the EXPLAIN feature comes in handy; it iterates through all the tables in your query and shows you what data it uses to find the specific row inside a table. If you query has a lot of JOINs this is very, very helpful.
The EXPLAIN function
First off, let's go through the EXPLAIN function. If we have a slow query that consists of a few LEFT JOINs, this is most often due to missing indices (indexes) to the joined tables.
SELECT id, title, year, release FROM movies LEFT JOIN releases ON movie_id=movies.id
If the releases table held a few thousand rows of release information (DVD, Blu-ray), the performance drops quickly if there is no sufficient index in the table. If we add the word EXPLAIN before the word SELECT, we will get a totally different result; just information about the query itself.
EXPLAIN SELECT id, title, year, release FROM movies LEFT JOIN releases ON movie_id=movies.id
Unfortunately, since these results are kind of hard to read since they're exported straight into the SQL client (I'm using HeidiSQL by the way), this article is about finding a better and more easy to understand way of reading the EXPLAIN output.
The EXPLAIN function works on both MariaDB and MySQL.
Export EXPLAIN as JSON
Luckily for us, this is as simple as just adding FORMAT=JSON to the SQL query.
So, instead of this:
EXPLAIN SELECT title FROM movies ...
Just write this:
EXPLAIN FORMAT=JSON SELECT title FROM movies ...
For once, this is something that is actually kind of straightforward.
Thanks, SQL developers!