Really long mysql query then really fast

Question

I have a weird behavior on SQL query that I really don't understand.

Here is my query:

SELECT DISTINCT SU.a 
FROM SU 
    INNER JOIN SUT ON SUT.id = SU.tid 
WHERE SUT.id = 1 
AND SUT.status != 'DELETED' 
AND SU.status != 'DELETED' 
ORDER BY SU.a 
LIMIT 1;

When it runs the first time, it takes 8 minutes to run:

# Time: 2021-07-13T15:14:24.655492Z
# User@Host: a @ localhost []  Id: 12778294
# Schema: a  Last_errno: 0  Killed: 0
# Query_time: 472.123927  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 2055628  Rows_affected: 0  Bytes_sent: 132

But when it runs the second time, it's really fast:

# Time: 2021-07-13T15:27:24.286939Z
# User@Host: a @ localhost []  Id: 12786055
# Schema: a  Last_errno: 0  Killed: 0
# Query_time: 3.099028  Lock_time: 0.000124  Rows_sent: 1  Rows_examined: 2055642  Rows_affected: 0  Bytes_sent: 132

The explain of the query seems good:

| id | select_type | table               | partitions | type  | possible_keys                                            | key                      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------------+------------+-------+----------------------------------------------------------+--------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | SUT | NULL       | const | PRIMARY                                                  | PRIMARY                  | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | SU         | NULL       | index | u_SU_a,idx_SUT_status | u_SU_a | 8       | NULL  | 4725 |     0.08 | Using where |
+----+-------------+---------------------+------------+-------+----------------------------------------------------------+--------------------------+---------+-------+------+----------+-------------+

The difference between both executions is really high. The host is a big VM with 55G ram and SSD Premium on Azure (P30 - 5000 IOPS, 200 MBps)

Answer 1

Short answer: What you see is normal.

Long answer:

If the second time were on the order of 0.001 seconds, I would suspect the Query cache. But since it is 3 seconds, I suspect it is other caching.

Note "Rows_examined" is about 2 million. In a really bad situation, this might be 2M disk hits. With HDD, this could take about 20K seconds; with SSD, perhaps under 2K seconds.

Since the first run took only 472 seconds, I suspect that the cache (the innodb_buffer_pool) was partially useful during the execution. Perhaps only 47200 blocks needed to be read (if using HDD).

The second run apparently found all the necessary blocks in the cache, making it CPU-bound, with no I/O. Hence, only 3 seconds.

A 3rd run would again be about 3 seconds. Etc.

Later on, if some other big query comes along and bumps blocks out of the cache, the query will again be slow.

If you provide SHOW CREATE TABLE for each table, we may be able to suggest better indexing to make it run faster both the first time and subsequent times.