6 Things You Shoud Check for MySQL Query Performance

In this post, I will explain some performance tuning tips for MySQL query.

1) Don't use wildcard (e.g. *) in SELECT (=Pick only required column on SELECT).

2) Comare performance between with and without 'JOIN'.

Sometimes JOIN helps MySQL engine will reduce examined number of rows.
But in my exeprience, most of the case, removing LEFT JOIN makes performance improvement.

3) Comare performance between with and without 'ORDER BY'.

4) Comare performance between with and without 'SQL_CALC_FOUND_ROWS'.

5) Check INDEX is properly used for JOIN, GROUP BY, ORDER BY.

6) Compare performance between following 2 strategy.

a) pick up all required columns (sometimes with ORDER BY, DISTINCT, LIMIT etc.) by single query
b) pick only required id column, and then pull all required columns using JOIN or execute 2nd query based on id columns picked up from the 1st query.
Okay the last 6) might need example. 6) says " comapre performance following a) and b) " query.

a) 
SELECT DISTINCT SQL_CALC_FOUND_ROWS
 t1.t1_id,
 t1.t1_columnA,
 t1.t1_columnB,
 t1.t1_columnC,
 t2.t1_columnA,
 t2.t1_columnB,
 t3.t3_columnA,
 t3.t3_columnB,
 t4.t4_columnA
FROM t1
INNER JOIN t2
ON t2.t1_id = t1.t1_id
LEFT JOIN t3
ON t3.t2_id = t2.t2_id
INNER JOIN t4
ON t4.t1_id = t1.t1_id
ORDER BY t1.t1_id
LIMIT 20, 20

b) 
SELECT DISTINCT SQL_CALC_FOUND_ROWS
 t1.t1_id
FROM t1
INNER JOIN t2
ON t2.t1_id = t1.t1_id
LEFT JOIN t3
ON t3.t2_id = t2.t2_id
INNER JOIN t4
ON t4.t1_id = t1.t1_id
ORDER BY t1.t1_id
LIMIT 20, 20

SELECT
 t1.t1_id,
 t1.t1_columnA,
 t1.t1_columnB,
 t1.t1_columnC,
 t2.t1_columnA,
 t2.t1_columnB,
 t3.t3_columnA,
 t3.t3_columnB,
 t4.t4_columnA
FROM t1
INNER JOIN t2
ON t2.t1_id = t1.t1_id
LEFT JOIN t3
ON t3.t2_id = t2.t2_id
INNER JOIN t4
ON t4.t1_id = t1.t1_id
WHERE t1.t1_id IN (...t1_ids selected by 1st query...)
ORDER BY t1.t1_id

Or if you don't need SQL_CALC_FOUND_ROWS, you can use single query.

b)
SELECT
 t1.t1_id,
 t1.t1_columnA,
 t1.t1_columnB,
 t1.t1_columnC,
 t2.t1_columnA,
 t2.t1_columnB,
 t3.t3_columnA,
 t3.t3_columnB,
 t4.t4_columnA
FROM t1
INNER JOIN t2
ON t2.t1_id = t1.t1_id
LEFT JOIN t3
ON t3.t2_id = t2.t2_id
INNER JOIN t4
ON t4.t1_id = t1.t1_id
INNER JOIN
(
    SELECT DISTINCT
     t1.t1_id
    FROM t1
    INNER JOIN t2
    ON t2.t1_id = t1.t1_id
    LEFT JOIN t3
    ON t3.t2_id = t2.t2_id
    INNER JOIN t4
    ON t4.t1_id = t1.t1_id
    ORDER BY t1.t1_id
    LIMIT 20, 20
) AS tmp
ON tmp.t1_id = t1.t1_id

And remember when measuring performance for MySQL query.

• You should definitely use 'EXPLAIN' keyword and investigate how the query will be executed!
• You should put 'SQL_NO_CACHE' when you mesure your query performance. Of course if your MySQL cache is disabled, you don't have to put the keyword.