Solution to slow page turning with Limit after MySQL data volume increases

1. Problem status

The existing MySQL data table event_data has a data volume of 300000 +, as follows:

mysql> select count(1) from event_data;
+----------+
| count(1) |
+----------+
|   367961 |
+----------+
1 row in set (0.05 sec)

It takes 382 seconds to use SQL limit paging query, as follows:

   SELECT a.*
     FROM event_data a
    WHERE a.receive_time >= '2018-03-28 00:00:00'
      AND a.receive_time <= '2018-03-28 23:59:59'
 ORDER BY a.receive_time DESC
    LIMIT 56280,15;

The receive time field has been indexed, and the primary key field of the event data table is PK ID.

 

2. Problem analysis

But if you change the above SQL slightly, you will find that the query speed has a qualitative leap, as follows:

mysql>    SELECT a.pk_id
    ->      FROM event_data a
    ->     WHERE a.receive_time >= '2018-03-28 00:00:00'
    ->       AND a.receive_time <= '2018-03-28 23:59:59'
    ->  ORDER BY a.receive_time DESC
    ->     LIMIT 56280,15;
+----------------------------------+
| pk_id                            |
+----------------------------------+
| ce7d990f39a4411c88ebb3240497e6f3 |
| bd47b1c380c946c39b3ec172a262823f |
| c1820a5633714a5e9a0b0c2abb092579 |
| d58456cbe16d4cc4a20645c1126fb9b7 |
| a1d786640ea048a7bd10bd9f12868d6d |
| f590aa4f26034dd9af2f4ba0d4f7430c |
| af3e81eb43b84467bd3e2c7ad31d6aff |
| 3ee331dce2064a788515986a0a97ac2b |
| 46f462dc4209499f90dad2dc4076e4ca |
| 8ebb7ff55bc443e4854e583f6dc37ff7 |
| 883fd68d93dc49eab5b35ebf9ab8f8ee |
| 932c264d6dd140f3ac4f07f3410ce147 |
| 19b6426cdd664fe2983166e0cca93c53 |
| 3a0bfa4e000e4b69bba1a6bed6545973 |
| 5640a162380346f19d57a61c1dc0dd42 |
+----------------------------------+
15 rows in set (0.03 sec)

It only takes 30 milliseconds for the query result to return. Although the SQL change is very small, it only changes the query result from a. * to a.pk_id, but the speed is greatly improved.

Cause analysis: two SQL where conditional statements use indexes. There is no problem here, but the query results are different. If only that index column (covering index) is included in the statement using index query, the query will be very fast, which is the case here.

By using overlay index, the query time can be reduced by minimizing the number of rows that need to be scanned by the query statement.

3. Problem solving

According to the above analysis, we use table connection to realize SQL optimization, as follows:

select a.* FROM (
			SELECT pk_id 
              FROM event_data c 
             WHERE c.receive_time >= '2018-03-28 00:00:00' 
               AND c.receive_time <= '2018-03-28 23:59:59' 
					ORDER BY c.receive_time DESC 
             LIMIT 56280,15
			    ) b 
	  left join event_data a 
			 on a.pk_id=b.pk_id

The execution time is 0.048 seconds to reach the optimization goal.

Tags: SQL MySQL

Posted on Sun, 05 Apr 2020 17:18:52 -0700 by CG_dude