[MDEV-8548] using LIMIT option makes query slower Created: 2015-07-27  Updated: 2022-09-12  Resolved: 2022-09-12

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0.17, 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: aftab khan Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: None

Attachments: Text File __create_table_info_and_variables.txt    

 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?



 Comments   
Comment by aftab khan [ 2015-07-27 ]

Another issue is that both versions of query return different result:

– Original Query - using LIMIT option

 
 
MariaDB [PRODUCTION]> SELECT ID_PROD_RESSOURCES_NAS  FROM (SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ORDER BY ID_PROD_RESSOURCES_NAS ASC) t LIMIT 1;
 
+------------------------+
 
| ID_PROD_RESSOURCES_NAS |
 
+------------------------+
 
|              115700116 |
 
+------------------------+
 
1 row in set (0.00 sec)

– Modified Query - using sub-query and outer query using LIMIT 

MariaDB [PRODUCTION]> SELECT ressource.ID_PROD_RESSOURCES_NAS FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ORDER BY ID_PROD_RESSOURCES_NAS ASC limit 1;
 
+------------------------+
 
| ID_PROD_RESSOURCES_NAS |
 
+------------------------+
 
|              115630756 |
 
+------------------------+
 
1 row in set (0.98 sec)

Comment by Elena Stepanova [ 2015-07-28 ]

Regarding the question in the comment about different results, see https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

Regarding the original issue with performance, please provide

  • EXPLAIN for the query with ORDER BY ... LIMIT;
  • EXPLAIN for the query with ORDER BY but without LIMIT;
  • execution time for both;
  • SHOW CREATE TABLE PROD_RESSOURCES_NAS output;
    and please also attach your cnf file(s).
    Thanks.
Comment by aftab khan [ 2015-07-29 ]

Hi Elena,

>Regarding the question in the comment about different results, see https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/
Thanks for sharing, it indeed explain the reason

>EXPLAIN for the query with ORDER BY ... LIMIT;

MariaDB [PRODUCTION]> EXPLAIN SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource  WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS 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 | 2000 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.01 sec)

>EXPLAIN for the query with ORDER BY but without LIMIT;

It uses correct index:

KEY `ID_RESSOURCE_STATUT_DATE_PASSAGE` (`ID_RESSOURCE_STATUT`,`DATE_PASSAGE`)
 

MariaDB [PRODUCTION]> EXPLAIN SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource  WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS ;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-----------------------------+
 
| 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 | 4857165 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-----------------------------+
 
1 row in set (0.01 sec)

However we can use INDEX HINT for the query with ORDER BY .. LIMIT to ensure correct execution plan:

 
 
MariaDB [PRODUCTION]> EXPLAIN SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE)  WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS 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 | 4857747 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-----------------------------+
 
1 row in set (0.01 sec)
 
 

Comment by Elena Stepanova [ 2015-08-01 ]

aftab.khan,
Thanks. As requested above, please also provide SHOW CREATE TABLE PROD_RESSOURCES_NAS output and cnf file(s).

Comment by aftab khan [ 2015-08-13 ]

Hi Elena, I have attached following file that has both SHOW CREATE + SHOW VARIABLES:
__create_table_info_and_variables.txt

Comment by Elena Stepanova [ 2015-08-20 ]

I cannot reproduce the problem so far.

I mean, I can easily get the described plans, but I don't see the plan with index/PRIMARY key perform any worse than the plan with ref/ID_RESSOURCE_STATUT_DATE_PASSAGE.

On the contrary, actually, see below. Result sets are redirected to /dev/null, it is only plans and execution times that we are interested in. 

MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
| id   | select_type | table     | type | possible_keys                    | key                              | key_len | ref   | rows    | filtered | Extra                       |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
|    1 | SIMPLE      | ressource | ref  | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9       | const | 1147132 |   100.00 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 

MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table     | type  | possible_keys                    | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | ressource | index | ID_RESSOURCE_STATUT_DATE_PASSAGE | PRIMARY | 8       | NULL | 2393 |   100.00 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 

MariaDB [test]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS ;
 
640000 rows in set (12.82 sec)
 
 
 
MariaDB [test]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (2.86 sec)

Of course, it might be caused by the artificially generated data, but at the very least it shows that the plan with ref/ID_RESSOURCE_STATUT_DATE_PASSAGE is not always beneficial.

Could you please provide the similar output – execution time for the query with LIMIT, without LIMIT, and with the LIMIT and index hint?
Please note that your configuration uses query cache, so make sure it does not affect the results (turn it off or use SQL_NO_CACHE as above).

Please also provide the output of show index in PROD_RESSOURCES_NAS, it will help me to generate data better resembling yours.

Comment by aftab khan [ 2015-08-20 ]

here is the output of the queries as requested:

 
 
MariaDB [PRODUCTION]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (32.02 sec)
 
 
 
MariaDB [PRODUCTION]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS ;
 
5088210 rows in set (51.23 sec)
 
 
 
MariaDB [PRODUCTION]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (2.52 sec)

I ran the first query again just to clarify that it is slow when not using index hints

MariaDB [PRODUCTION]> SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (7.64 sec)
 
 

I would also like to share the distribution of rows by ID_RESOURCE_STATUS

MariaDB [PRODUCTION]> SELECT ID_RESSOURCE_STATUT, COUNT(*) FROM PROD_RESSOURCES_NAS GROUP BY ID_RESSOURCE_STATUT HAVING COUNT(*) > 1;
 
+---------------------+----------+
 
| ID_RESSOURCE_STATUT | COUNT(*) |
 
+---------------------+----------+
 
|                   0 |  8467961 |
 
|                   1 |  5087710 |
 
|                   2 |  4479212 |
 
|                   3 |   433788 |
 
+---------------------+----------+
 
4 rows in set (1 min 19.20 sec)
 
 
 
-- show index ouput
 
 
 
MariaDB [PRODUCTION]> SHOW KEYS IN PROD_RESSOURCES_NAS;
 
+---------------------+------------+----------------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table               | Non_unique | Key_name                                     | Seq_in_index | Column_name              | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+---------------------+------------+----------------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| PROD_RESSOURCES_NAS |          0 | PRIMARY                                      |            1 | ID_PROD_RESSOURCES_NAS   | A         |    16747463 |     NULL | NULL   |      | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | idx_prnas_item_type_composant                |            1 | ID_PROD_ITEM             | A         |      523358 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | idx_prnas_item_type_composant                |            2 | TYPE_RESSOURCE           | A         |     1860829 |     NULL | NULL   |      | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | idx_prnas_item_type_composant                |            3 | COMPOSANT                | A         |     1860829 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_PROD_ETAPE_TRAITEMENT                  |            1 | ID_PROD_ETAPE_TRAITEMENT | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_PROD_COMPOSANT_PHOTO                   |            1 | ID_PROD_COMPOSANT_PHOTO  | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_PROD_COMMANDE_PRN                      |            1 | ID_PROD_COMMANDE         | A         |      190312 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | IDX_COMPOSANT                                |            1 | ID_PROD_COMPOSANT        | A         |    16747463 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_LOTIFICATION_RESOURCE_STATUT_PROD_ITEM |            1 | ID_LOTIFICATION          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_LOTIFICATION_RESOURCE_STATUT_PROD_ITEM |            2 | ID_RESSOURCE_STATUT      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | FK_ID_LOTIFICATION_RESOURCE_STATUT_PROD_ITEM |            3 | ID_PROD_ITEM             | A         |      315989 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | ID_RESSOURCE_STATUT_DATE_PASSAGE             |            1 | ID_RESSOURCE_STATUT      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
 
| PROD_RESSOURCES_NAS |          1 | ID_RESSOURCE_STATUT_DATE_PASSAGE             |            2 | DATE_PASSAGE             | A         |     5582487 |     NULL | NULL   |      | BTREE      |         |               |
 
+---------------------+------------+----------------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
13 rows in set (0.01 sec)
 
 
 
-- EXPLAIN outputs
 
 
 
MariaDB [PRODUCTION]> EXPLAIN SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS 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 | 8373733 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+-----------------------------+
 
1 row in set (0.00 sec)

Explain output of the query not using INDEX HINTS looks more optimal but query takes longer to complete

MariaDB [PRODUCTION]> EXPLAIN SELECT SQL_NO_CACHE ressource.* FROM PROD_RESSOURCES_NAS ressource  WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS 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 | 2000 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.01 sec)
 
 

Comment by Elena Stepanova [ 2015-08-20 ]

I crafted the data where I can reproduce the performance problem.
What I did is put the desired ID_RESSOURCE_STATUT= 1 at the very end of the data ordered by the primary key.
You may have it somewhat different, but I suppose the idea is the same.

Here is how it looks in my case:

Data

Value distribution

 
ariaDB [test]> SELECT ID_RESSOURCE_STATUT, COUNT(*) FROM PROD_RESSOURCES_NAS GROUP BY ID_RESSOURCE_STATUT;
 
+---------------------+----------+
 
| ID_RESSOURCE_STATUT | COUNT(*) |
 
+---------------------+----------+
 
|                   0 |   920000 |
 
|                   1 |   650000 |
 
|                   2 |   510000 |
 
|                   3 |   510000 |
 
|                   4 |   410000 |
 
+---------------------+----------+
 
5 rows in set (1.81 sec)

PK values

 
MariaDB [test]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                           1 |                     3000000 |
 
+-----------------------------+-----------------------------+
 
1 row in set (0.00 sec)

ID_RESSOURCE_STATUT=1 is at the very end of the table

 
MariaDB [test]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS WHERE ID_RESSOURCE_STATUT = 1;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                     2350001 |                     3000000 |
 
+-----------------------------+-----------------------------+
 
1 row in set (0.24 sec)

ID_RESSOURCE_STATUT=0 is distributed across the table

 
MariaDB [test]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS WHERE ID_RESSOURCE_STATUT = 0;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                           1 |                     2350000 |
 
+-----------------------------+-----------------------------+
 
1 row in set (0.58 sec)

Results for server with all defaults (importantly, without the performance schema)

Plans for ID_RESSOURCE_STATUT=1

 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table     | type  | possible_keys                    | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | ressource | index | ID_RESSOURCE_STATUT_DATE_PASSAGE | PRIMARY | 8       | NULL | 2150 |   100.00 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
| id   | select_type | table     | type | possible_keys                    | key                              | key_len | ref   | rows    | filtered | Extra                       |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
|    1 | SIMPLE      | ressource | ref  | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9       | const | 1310334 |   100.00 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
| id   | select_type | table     | type | possible_keys                    | key                              | key_len | ref   | rows    | filtered | Extra                       |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
|    1 | SIMPLE      | ressource | ref  | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9       | const | 1310334 |   100.00 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+

Plans for ID_RESSOURCE_STATUT=0

 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table     | type  | possible_keys                    | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | ressource | index | ID_RESSOURCE_STATUT_DATE_PASSAGE | PRIMARY | 8       | NULL | 2000 |   100.00 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
| id   | select_type | table     | type | possible_keys                    | key                              | key_len | ref   | rows    | filtered | Extra                       |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
|    1 | SIMPLE      | ressource | ref  | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9       | const | 1408966 |   100.00 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS;
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
| id   | select_type | table     | type | possible_keys                    | key                              | key_len | ref   | rows    | filtered | Extra                       |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
|    1 | SIMPLE      | ressource | ref  | ID_RESSOURCE_STATUT_DATE_PASSAGE | ID_RESSOURCE_STATUT_DATE_PASSAGE | 9       | const | 1408966 |   100.00 | Using where; Using filesort |
 
+------+-------------+-----------+------+----------------------------------+----------------------------------+---------+-------+---------+----------+-----------------------------+
 
1 row in set, 1 warning (0.00 sec)

Execution time for ID_RESSOURCE_STATUT=1

 
ariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (5.46 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (0.94 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS;
 
650000 rows in set (3.25 sec)

Execution time for ID_RESSOURCE_STATUT=0

 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (0.79 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS;
 
920000 rows in set (11.92 sec)

As you can see, for 0 it works as expected.

Results for server with performance schema enabled

I put them separately, because even though the ratio holds, the values are essentially different.

Execution time for ID_RESSOURCE_STATUT=1

 
ariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (10.76 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (2.81 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS;
 
650000 rows in set (7.58 sec)

Execution time for ID_RESSOURCE_STATUT=0

 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (4.30 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 0 ORDER BY ID_PROD_RESSOURCES_NAS;
 
920000 rows in set (14.27 sec)

ANALYZE does not help:

Execution time after ANALYZE

 
MariaDB [test]> ANALYZE TABLE PROD_RESSOURCES_NAS;
 
1 row in set (0.30 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (11.02 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (2.93 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS;
 
650000 rows in set (8.13 sec)

Engine-independent statistics also does not help:

MariaDB [test]> set use_stat_tables = PREFERABLY;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> set optimizer_use_condition_selectivity = 4;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> set histogram_size = 255;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> ANALYZE TABLE PROD_RESSOURCES_NAS;
 
+--------------------------+---------+----------+-----------------------------------------+
 
| Table                    | Op      | Msg_type | Msg_text                                |
 
+--------------------------+---------+----------+-----------------------------------------+
 
| test.PROD_RESSOURCES_NAS | analyze | status   | Engine-independent statistics collected |
 
| test.PROD_RESSOURCES_NAS | analyze | status   | OK                                      |
 
+--------------------------+---------+----------+-----------------------------------------+
 
2 rows in set (2 min 57.53 sec)

Execution time with EITS

 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (10.78 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource USE INDEX (ID_RESSOURCE_STATUT_DATE_PASSAGE) WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS LIMIT 1000;
 
1000 rows in set (2.85 sec)
 
 
 
MariaDB [test]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ID_RESSOURCE_STATUT= 1 ORDER BY ID_PROD_RESSOURCES_NAS;
 
650000 rows in set (7.51 sec)

I uploaded the data dump here: ftp://ftp.askmonty.org/public/mdev8548_data.dump.gz
With this, I am reassigning it to psergey for further analysis and consideration, although I am not sure the optimizer is able to predict that the chosen index is not best with this particular data distribution.

Comment by aftab khan [ 2015-08-20 ]

Hi Elena,

Looks like we also have ID_RESOURCE_ID=1 at the very end of the data ordered by the primary key.

PK value boundries:

MariaDB [PRODUCTION]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                    34509533 |                    55443581 |
 
+-----------------------------+-----------------------------+
 
1 row in set (0.01 sec)
 
 

ID_RESSOURCE_STATUT=1 is at the very end of the table

MariaDB [PRODUCTION]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS WHERE ID_RESSOURCE_STATUT = 1;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                    42546877 |                    50457253 |
 
+-----------------------------+-----------------------------+
 
1 row in set (2.05 sec)

ID_RESSOURCE_STATUT=0 is distributed across the table

MariaDB [PRODUCTION]> SELECT MIN(ID_PROD_RESSOURCES_NAS), MAX(ID_PROD_RESSOURCES_NAS) FROM PROD_RESSOURCES_NAS WHERE ID_RESSOURCE_STATUT = 0;
 
+-----------------------------+-----------------------------+
 
| MIN(ID_PROD_RESSOURCES_NAS) | MAX(ID_PROD_RESSOURCES_NAS) |
 
+-----------------------------+-----------------------------+
 
|                    34896389 |                    55443766 |
 
+-----------------------------+-----------------------------+
 
1 row in set (3.49 sec)
 
 

Comment by aftab khan [ 2015-08-21 ]

Hi,

I think we have not provided query times without INDEX HINT, the results are interesting:

Execution time for ID_RESSOURCE_STATUT=0

 
 
MariaDB [PRODUCTION]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 0 ORDER BY ID_PROD_RESSOURCES_NAS ASC LIMIT 1000;
 
1000 rows in set (0.03 sec)
 
 
 
-- Count of ID_RESOURCE_STATUS = 0
 
 
 
MariaDB [PRODUCTION]> SELECT COUNT(*) FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 0 ORDER BY ID_PROD_RESSOURCES_NAS ASC;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|  7459826 |
 
+----------+
 
1 row in set (3.01 sec)
 
 
 
 
 
-- EXPLAIN ouput
 
 
 
MariaDB [PRODUCTION]> EXPLAIN  SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 0 ORDER BY ID_PROD_RESSOURCES_NAS ASC LIMIT 1;
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
 
| 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 |    2 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.00 sec)

Execution time for ID_RESSOURCE_STATUT=1

MariaDB [PRODUCTION]> SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ORDER BY ID_PROD_RESSOURCES_NAS ASC LIMIT 1000;
 
1000 rows in set (8.40 sec)
 
 
 
-- Count of ID_RESOURCE_STATUS = 0
 
 
 
MariaDB [PRODUCTION]> SELECT COUNT(*) FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ORDER BY ID_PROD_RESSOURCES_NAS ASC LIMIT 1000;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|  4176210 |
 
+----------+
 
1 row in set (1.68 sec)
 
 
 
 -- EXPLAIN output
 
 
 
MariaDB [PRODUCTION]> EXPLAIN SELECT ressource.* FROM PROD_RESSOURCES_NAS ressource WHERE ressource.ID_RESSOURCE_STATUT = 1 ORDER BY 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 | 2009 | Using where |
 
+------+-------------+-----------+-------+----------------------------------+---------+---------+------+------+-------------+
 
1 row in set (0.00 sec)
 
 

Observations:

1. Total row count is 7459826 where ID_RESSOURCE_STATUT=0 but data is distributed across the table, query execution time is 0.03 sec
2. Total row count is 4176210 where ID_RESSOURCE_STATUT=1 but data is some where mid-end of the table, query execution time is 8.40 sec

Comment by Sergei Golubchik [ 2022-09-12 ]

10.0 was EOLed in March 2019

Generated at Thu Feb 08 07:27:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.