How to Optimize MySQL Query Execution Time for Large Datasets?

MySQL Query Execution Time Optimization

When working with large databases, query execution time becomes a crucial factor. In one such scenario, a website database with 1 million records encountered excessive execution times for queries. One sample query, presented below, demonstrated this issue:

select * from `ratings` order by id limit 499500, 500

Despite the belief that handling 1 million records in MySQL tables should not pose problems, this query consistently took more than a minute to execute. The addition of an index on the id column failed to mitigate this issue.

However, upon examining the EXPLAIN plan for this query, it was discovered that the query was using a full table scan approach:

explain select * from `ratings` order by id limit 499500, 500;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | ratings | ALL  | NULL          | NULL | NULL    | NULL | 4718592 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

To address this issue, it was suggested that a where clause be utilized to narrow down the search criteria. When a where clause was added, the query plan changed:

explain select * from `ratings` where id>=499501 limit 500;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                 |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
|  1 | SIMPLE      | ratings | range | PRIMARY       | PRIMARY | 4       | NULL | 4198581 | Using index condition |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+

This change resulted in the query using an index condition approach, significantly improving its execution time.

Additionally, the possibility of a deadlock in the query execution process could not be ruled out. To diagnose deadlocks, the SHOW INNODB STATUS command may be helpful.

The above is the detailed content of How to Optimize MySQL Query Execution Time for Large Datasets?. For more information, please follow other related articles on the PHP Chinese website!