Details
Major
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?
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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)
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 Sergei Petrunia 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.
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)
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
Another issue is that both versions of query return different result:
– Original Query - using LIMIT option
– Modified Query - using sub-query and outer query using LIMIT