Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0.17, 10.0.21
-
None
Description
Query below returns 1000 rows in sorted order:
 |
EXPLAIN SELECT ressource.*
|
-> FROM PROD_RESSOURCES_NAS ressource
|
-> WHERE ressource.ID_RESSOURCE_STATUT = 1
|
-> ORDER BY ID_RESSOURCE_STATUT, ID_PROD_RESSOURCES_NAS ASC
|
-> LIMIT 1000;
|
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | ressource | index | ID_RESSOURCE_STATUT_DATE_PASSAGE | PRIMARY | 8 | NULL | 6036 | Using where |
|
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
|
1 row in set (0.04 sec)
|
It takes 1-3 minutes to return result set, however if we do not use ORDER BY then of course its very faster. Similarly if we do not use 'LIMIT N' option then query start to return all rows quickly but we require only N number of rows:
MariaDB [PRODUCTION]> SELECT COUNT(*) FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ;
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 2665493 |
|
+----------+
|
1 row in set (1.16 sec)
|
As work around we are using sub-query to help fix this performance issue:
MariaDB [PRODUCTION]> EXPLAIN SELECT *
|
-> FROM
|
-> (SELECT ressource.*
|
-> FROM PROD_RESSOURCES_NAS ressource
|
-> WHERE ressource.ID_RESSOURCE_STATUT = 1
|
-> ORDER BY ID_PROD_RESSOURCES_NAS ASC ) t
|
-> LIMIT 1000;
|
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-------+
|
| 1 | SIMPLE | ressource | ref | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9 | const | 6933758 | |
|
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-------+
|
1 row in set (0.01 sec)
|
Why does LIMIT option make it slower?