How to Speed Up MySQL Queries with Slow Query Log, EXPLAIN, and Indexes

Enable the Slow Query Log

Open the MySQL configuration file my.cnf and set the following parameters:

slow_query_log = On
long_query_time = 0.2
slow_query_log_file = /path/to/slow.log

Restart MySQL. Any query whose execution time exceeds long_query_time will be recorded in the specified log file, allowing you to identify the most time‑consuming statements.

Analyze Queries with EXPLAIN

For each slow query, prepend the EXPLAIN keyword (compatible with SELECT, DELETE, INSERT, REPLACE, UPDATE) and run it. The output shows one row per table involved, indicating the table name, the key (index) used, and the number of rows examined.

Typical EXPLAIN output highlights which tables are scanned fully and which indexes are applied. In the example, the query scans 2 million rows from picture and, for each, 20 thousand rows from album, resulting in an effective scan of 400 billion rows.

Add Indexes to Reduce Scans

Create an index on the column used to join the tables, for example:

CREATE INDEX idx_picture_album_id ON picture(album_id);
CREATE INDEX idx_album_user_id ON album(user_id);

After adding the index on picture.album_id, the query no longer scans the entire picture table. It first scans the album table to find the relevant albums, then uses the index to locate matching pictures, reducing scanned rows to about 200 k and improving speed roughly 317‑fold.

Adding a matching index on album.user_id ensures the album table is also accessed via an index, further cutting the scan to a few hundred rows and delivering an overall speedup of about 380‑times compared with the original query.

Results and Trade‑offs

The indexing strategy dramatically reduces the number of rows examined and accelerates query execution, but each index adds overhead to write operations. Therefore, add indexes only when the read‑performance gain outweighs the additional write cost. Use EXPLAIN to verify that the optimizer actually employs the new indexes; if an index is not used, consider removing it.