Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1.28
-
Linux (Ubuntu)
Description
I identified an issue which happens on MariaDb because a query is very very slow compared to the same query executed on the same database schema / data /engine (innodb) in Mysql. I give you the figures to show the difference for this query.
SELECT SQL_NO_CACHE t0_.ID_TICKETS AS ID_TICKETS_0, t0_.MSISDN AS MSISDN_1, t0_.IMMAT AS IMMAT_2, t0_.OPERATEUR AS OPERATEUR_3, t0_.DHDEBUT AS DHDEBUT_4, t0_.DHMAX AS DHMAX_5, t0_.DHFIN AS DHFIN_6, t0_.PRIX AS PRIX_7, t0_.TYPE_APPEL AS TYPE_APPEL_8, t0_.PAYS AS PAYS_9, t1_.ID AS ID_10, t1_.DHTRANS AS DHTRANS_11, t1_.MONTANT AS MONTANT_12, t0_.ID_CITOYENS AS ID_CITOYENS_13, t0_.ID_ZONES AS ID_ZONES_14, t0_.STATUT AS STATUT_15, t0_.ID_TRANSACTIONS AS ID_TRANSACTIONS_16, t1_.ID_CITOYENS AS ID_CITOYENS_17, t1_.STATUT AS STATUT_18, t1_.locality_id AS locality_id_19 |
FROM TICKETS t0_ LEFT JOIN TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID |
INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) |
ORDER BY t0_.DHDEBUT ASC LIMIT 10 OFFSET 0; |
MariaDb | 10.1.28 => 12s
Mysql | 5.7.20 => 0.00s
desc TICKETS;
+-----------------+-------------+------+-----+---------+----------------+ |
| Field | Type | Null | Key | Default | Extra | |
+-----------------+-------------+------+-----+---------+----------------+ |
| ID_TICKETS | int(11) | NO | PRI | NULL | auto_increment | |
| ID_CITOYENS | int(11) | NO | MUL | NULL | | |
| ID_ZONES | int(11) | NO | MUL | NULL | | |
| MSISDN | varchar(20) | YES | MUL | NULL | | |
| IMMAT | varchar(20) | YES | MUL | NULL | | |
| OPERATEUR | int(11) | NO | | NULL | | |
| DHDEBUT | datetime | NO | MUL | NULL | | |
| DHMAX | datetime | NO | | NULL | | |
| DHFIN | datetime | YES | | NULL | | |
| PRIX | int(11) | NO | | NULL | | |
| STATUT | int(11) | NO | MUL | NULL | | |
| TYPE_APPEL | varchar(10) | NO | | NULL | | |
| PAYS | varchar(3) | NO | | NULL | | |
| ID_TRANSACTIONS | int(11) | YES | MUL | NULL | | |
+-----------------+-------------+------+-----+---------+----------------+ |
EXPLAIN Query Mysql
+----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+ |
| 1 | SIMPLE | t0_ | NULL | index | IDX_6B0363EEAAD06922 | DHDEBUT | 5 | NULL | 10 | 100.00 | NULL | |
| 1 | SIMPLE | z2_ | NULL | eq_ref | PRIMARY,IDX_729E73D8AAFE1877 | PRIMARY | 4 | CALL2PARK.t0_.ID_ZONES | 1 | 100.00 | Using where | |
| 1 | SIMPLE | t1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | CALL2PARK.t0_.ID_TRANSACTIONS | 1 | 100.00 | NULL | |
+----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+ |
EXPLAIN Query MariaDb
+----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+----------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+----------------------------------------------+ |
| 1 | SIMPLE | z2_ | ref | PRIMARY,IDX_729E73D8AAFE1877 | IDX_729E73D8AAFE1877 | 4 | const | 14 | Using index; Using temporary; Using filesort | |
| 1 | SIMPLE | t0_ | ref | IDX_6B0363EEAAD06922 | IDX_6B0363EEAAD06922 | 4 | call2park.z2_.ID | 49471 | |
|
| 1 | SIMPLE | t1_ | eq_ref | PRIMARY | PRIMARY | 4 | call2park.t0_.ID_TRANSACTIONS | 1 | Using where | |
+----+-------------+-------+--------+--------------------------------+--------------- |
If I remove some properties from the select in query it's a lot faster. It's very strange because I get only 10 rows.
SELECT SQL_NO_CACHE t0_.ID_TICKETS AS ID_TICKETS_0, t1_.ID AS ID_10 |
FROM TICKETS t0_ LEFT JOIN TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID |
INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) ORDER BY t0_.DHDEBUT ASC LIMIT 10 OFFSET 0; |
MariaDb | 10.1.28 => 3s
If I keep all properties in select but remove the "order by" clause it takes less than 50ms.
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
-
- Stalled
-
-
-
- Closed
-
Activity
CREATE TABLE `TICKETS` ( |
`ID_TICKETS` int(11) NOT NULL AUTO_INCREMENT, |
`ID_CITOYENS` int(11) NOT NULL, |
`ID_ZONES` int(11) NOT NULL, |
`MSISDN` varchar(20) DEFAULT NULL, |
`IMMAT` varchar(20) DEFAULT NULL, |
`OPERATEUR` int(11) NOT NULL, |
`DHDEBUT` datetime NOT NULL, |
`DHMAX` datetime NOT NULL, |
`DHFIN` datetime DEFAULT NULL, |
`PRIX` int(11) NOT NULL, |
`STATUT` int(11) NOT NULL, |
`TYPE_APPEL` varchar(10) NOT NULL, |
`PAYS` varchar(3) NOT NULL, |
`ID_TRANSACTIONS` int(11) DEFAULT NULL, |
PRIMARY KEY (`ID_TICKETS`), |
KEY `IDX_6B0363EE9BC9A2E0` (`ID_CITOYENS`), |
KEY `IDX_6B0363EEAAD06922` (`ID_ZONES`), |
KEY `IDX_6B0363EE19FA6068` (`ID_TRANSACTIONS`), |
KEY `IDX_6B0363EE159C3739` (`STATUT`), |
KEY `DHDEBUT` (`DHDEBUT`), |
KEY `MSISDN` (`MSISDN`), |
KEY `IMMAT` (`IMMAT`), |
CONSTRAINT `FK_6B0363EE159C3739` FOREIGN KEY (`STATUT`) REFERENCES `ticket_status` (`id`), |
CONSTRAINT `FK_6B0363EE19FA6068` FOREIGN KEY (`ID_TRANSACTIONS`) REFERENCES `TRANSACTIONS` (`ID`), |
CONSTRAINT `FK_6B0363EE9BC9A2E0` FOREIGN KEY (`ID_CITOYENS`) REFERENCES `CITOYENS` (`ID`), |
CONSTRAINT `FK_6B0363EEAAD06922` FOREIGN KEY (`ID_ZONES`) REFERENCES `ZONES` (`ID`) |
) ENGINE=InnoDB AUTO_INCREMENT=1211763 DEFAULT CHARSET=utf8 |
|
|
CREATE TABLE `TRANSACTIONS` ( |
`ID` int(11) NOT NULL AUTO_INCREMENT, |
`ID_CITOYENS` int(11) DEFAULT NULL, |
`DHTRANS` datetime NOT NULL, |
`MONTANT` int(11) NOT NULL, |
`STATUT` int(11) NOT NULL, |
`locality_id` int(11) DEFAULT NULL, |
PRIMARY KEY (`ID`), |
KEY `IDX_7F9A535C9BC9A2E0` (`ID_CITOYENS`), |
KEY `IDX_7F9A535C159C3739` (`STATUT`), |
KEY `IDX_7F9A535C88823A92` (`locality_id`), |
CONSTRAINT `FK_7F9A535C159C3739` FOREIGN KEY (`STATUT`) REFERENCES `transaction_status` (`id`), |
CONSTRAINT `FK_7F9A535C88823A92` FOREIGN KEY (`locality_id`) REFERENCES `LOCALITES` (`ID`), |
CONSTRAINT `FK_7F9A535C9BC9A2E0` FOREIGN KEY (`ID_CITOYENS`) REFERENCES `CITOYENS` (`ID`) |
) ENGINE=InnoDB AUTO_INCREMENT=353034 DEFAULT CHARSET=utf8 |
|
|
CREATE TABLE `ZONES` ( |
`ID` int(10) NOT NULL AUTO_INCREMENT, |
`ZONE` int(4) NOT NULL, |
`DUREEPERIODE` int(4) NOT NULL, |
`DUREEMAX` int(4) NOT NULL, |
`PERIODEINDIV` tinyint(1) NOT NULL, |
`FERIE` tinyint(1) NOT NULL, |
`ID_LOCALITE` int(11) NOT NULL, |
PRIMARY KEY (`ID`), |
KEY `IDX_729E73D8AAFE1877` (`ID_LOCALITE`), |
CONSTRAINT `FK_729E73D8AAFE1877` FOREIGN KEY (`ID_LOCALITE`) REFERENCES `LOCALITES` (`ID`) |
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 |
Please note that I have the same my.cnf for mariadb and mysql.
Reproduced on MariaDB 10.1-10.3.
(index on column, that is used in order by not used vs force use of that index)
CREATE TABLE t0 ( |
ID_t0 int NOT NULL AUTO_INCREMENT PRIMARY KEY, |
ID_z2 int NOT NULL, |
d datetime NOT NULL, |
ID_t1 int , |
KEY (ID_z2), |
KEY (ID_t1), |
KEY (d)); |
INSERT INTO t0 SELECT seq,seq,now(),1 FROM seq_1_to_500000; |
|
|
CREATE TABLE t1 (ID int NOT NULL AUTO_INCREMENT PRIMARY KEY) ; |
INSERT INTO t1 SELECT seq FROM seq_1_to_500000; |
|
|
CREATE TABLE z2 ( |
ID int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, |
ID_LOCALITE int NOT NULL, |
KEY (ID_LOCALITE)); |
INSERT INTO z2 SELECT seq,1 FROM seq_1_to_500000; |
|
|
SELECT SQL_NO_CACHE t0.ID_t0 , t1.ID |
FROM t0 FORCE INDEX (d) |
LEFT JOIN t1 t1 ON t0.ID_t1 = t1.ID |
INNER JOIN z2 z2 ON t0.ID_z2 = z2.ID AND (z2.ID_LOCALITE = 1) |
ORDER BY t0.d ASC LIMIT 10 ; |
|
|
SELECT SQL_NO_CACHE t0.ID_t0 , t1.ID |
FROM t0 |
LEFT JOIN t1 t1 ON t0.ID_t1 = t1.ID |
INNER JOIN z2 z2 ON t0.ID_z2 = z2.ID AND (z2.ID_LOCALITE = 1) |
ORDER BY t0.d ASC LIMIT 10 ; |
+------+-------------+-------+--------+---------------------+---------+---------+---------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------------+---------+---------+---------------+------+----------+--------------------------+
|
| 1 | SIMPLE | t0 | index | NULL | d | 5 | NULL | 10 | 100.00 | |
|
| 1 | SIMPLE | z2 | eq_ref | PRIMARY,ID_LOCALITE | PRIMARY | 4 | test.t0.ID_z2 | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t0.ID_t1 | 1 | 100.00 | Using where; Using index |
|
+------+-------------+-------+--------+---------------------+---------+---------+---------------+------+----------+--------------------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
|
Note (Code 1003): select sql_no_cache `test`.`t0`.`ID_t0` AS `ID_t0`,`test`.`t1`.`ID` AS `ID` from `test`.`t0` FORCE INDEX (`d`) left join `test`.`t1` on(((`test`.`t1`.`ID` = `test`.`t0`.`ID_t1`) and (`test`.`t0`.`ID_t1` is not null))) join `test`.`z2` where ((`test`.`z2`.`ID` = `test`.`t0`.`ID_z2`) and (`test`.`z2`.`ID_LOCALITE` = 1)) order by `test`.`t0`.`d` limit 10
|
+------+-------------+-------+--------+---------------------+-------------+---------+---------------+--------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------------+-------------+---------+---------------+--------+----------+----------------------------------------------+
|
| 1 | SIMPLE | z2 | ref | PRIMARY,ID_LOCALITE | ID_LOCALITE | 4 | const | 250000 | 100.00 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | t0 | ref | ID_z2 | ID_z2 | 4 | test.z2.ID | 1 | 100.00 | |
|
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t0.ID_t1 | 1 | 100.00 | Using where; Using index |
|
+------+-------------+-------+--------+---------------------+-------------+---------+---------------+--------+----------+----------------------------------------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
|
Note (Code 1003): select sql_no_cache `test`.`t0`.`ID_t0` AS `ID_t0`,`test`.`t1`.`ID` AS `ID` from `test`.`t0` left join `test`.`t1` on(((`test`.`t1`.`ID` = `test`.`t0`.`ID_t1`) and (`test`.`t0`.`ID_t1` is not null))) join `test`.`z2` where ((`test`.`t0`.`ID_z2` = `test`.`z2`.`ID`) and (`test`.`z2`.`ID_LOCALITE` = 1)) order by `test`.`t0`.`d` limit 10
|
+-------+------+
|
| ID_t0 | ID |
|
+-------+------+
|
| 1 | 1 |
|
| 2 | 1 |
|
| 3 | 1 |
|
| 4 | 1 |
|
| 5 | 1 |
|
| 6 | 1 |
|
| 7 | 1 |
|
| 8 | 1 |
|
| 9 | 1 |
|
| 10 | 1 |
|
+-------+------+
|
10 rows in set (0.00 sec)
|
|
|
+--------+------+
|
| ID_t0 | ID |
|
+--------+------+
|
| 390962 | 1 |
|
| 395058 | 1 |
|
| 398899 | 1 |
|
| 402995 | 1 |
|
| 406836 | 1 |
|
| 410932 | 1 |
|
| 414773 | 1 |
|
| 418869 | 1 |
|
| 422710 | 1 |
|
| 426806 | 1 |
|
+--------+------+
|
10 rows in set (1.65 sec)
|
Ok,
- the fast query is able to use the index that matches the ORDER BY (I assume this allows it to enumerate only a small fraction of rows in the join)
- the slow query uses "Using temporary; Using filesort", which means it has to enumerate all rows in the join. This is most likely the reason why it's slow.
The slow query uses a join order that doesn't allow to use an index to resolve the ORDER BY.
This is a long known problem, both in MariaDB and MySQL: join optimization is performed with [almost] no regard to whether the produced join orders allow efficient handling of ORDER BY ... LIMIT. (this is filed against MariaDB as MDEV-8306)
My guess would be that in this example:
- MySQL picks a suitable join order (by chance). Then it finds out that the join order allows to resolve ORDER BY efficiently, and the query is fast.
- MariaDB picks a non-suitable join order (by chance). Then, it has to use an inefficient way to resolve the ORDER BY.
The ORDER BY clause is ORDER BY t0_.DHDEBUT ASC . This column is not used anywhere in WHERE/ON clauses, so "orderby_uses_equalities" is of no help here.
It would be nice to confirm the above. raziel057, is it possible for you to get the EXPLAINs for the query without ORDER BY, preferably on both MariaDB and MySQL:
EXPLAIN
|
SELECT SQL_NO_CACHE |
t0_.ID_TICKETS AS ID_TICKETS_0, |
t0_.MSISDN AS MSISDN_1, |
t0_.IMMAT AS IMMAT_2, |
t0_.OPERATEUR AS OPERATEUR_3, |
t0_.DHDEBUT AS DHDEBUT_4, |
t0_.DHMAX AS DHMAX_5, |
t0_.DHFIN AS DHFIN_6, |
t0_.PRIX AS PRIX_7, |
t0_.TYPE_APPEL AS TYPE_APPEL_8, |
t0_.PAYS AS PAYS_9, |
t1_.ID AS ID_10, |
t1_.DHTRANS AS DHTRANS_11, |
t1_.MONTANT AS MONTANT_12, |
t0_.ID_CITOYENS AS ID_CITOYENS_13, |
t0_.ID_ZONES AS ID_ZONES_14, |
t0_.STATUT AS STATUT_15, |
t0_.ID_TRANSACTIONS AS ID_TRANSACTIONS_16, |
t1_.ID_CITOYENS AS ID_CITOYENS_17, |
t1_.STATUT AS STATUT_18, |
t1_.locality_id AS locality_id_19 |
FROM |
TICKETS t0_
|
LEFT JOIN |
TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID |
INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) |
Also, let's see what happens on MariaDB if the join optimizer is forced to pick a good join order: can you run both EXPLAIN and SELECT itself on MariaDB:
EXPLAIN
|
SELECT SQL_NO_CACHE |
t0_.ID_TICKETS AS ID_TICKETS_0, |
t0_.MSISDN AS MSISDN_1, |
t0_.IMMAT AS IMMAT_2, |
t0_.OPERATEUR AS OPERATEUR_3, |
t0_.DHDEBUT AS DHDEBUT_4, |
t0_.DHMAX AS DHMAX_5, |
t0_.DHFIN AS DHFIN_6, |
t0_.PRIX AS PRIX_7, |
t0_.TYPE_APPEL AS TYPE_APPEL_8, |
t0_.PAYS AS PAYS_9, |
t1_.ID AS ID_10, |
t1_.DHTRANS AS DHTRANS_11, |
t1_.MONTANT AS MONTANT_12, |
t0_.ID_CITOYENS AS ID_CITOYENS_13, |
t0_.ID_ZONES AS ID_ZONES_14, |
t0_.STATUT AS STATUT_15, |
t0_.ID_TRANSACTIONS AS ID_TRANSACTIONS_16, |
t1_.ID_CITOYENS AS ID_CITOYENS_17, |
t1_.STATUT AS STATUT_18, |
t1_.locality_id AS locality_id_19 |
FROM |
(TICKETS t0_ straight_join ZONES z2_ )
|
LEFT JOIN |
TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID |
WHERE
|
t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) |
ORDER BY |
t0_.DHDEBUT ASC |
LIMIT 10 OFFSET 0;
|
and post EXPLAIN output and the execution time for the SELECT?
Thanks,
EXPLAIN SELECT SQL_NO_CACHE t0_.ID_TICKETS AS ID_TICKETS_0, t0_.MSISDN AS MSISDN_1, t0_.IMMAT AS IMMAT_2, t0_.OPERATEUR AS OPERATEUR_3, t0_.DHDEBUT AS DHDEBUT_4, t0_.DHMAX AS DHMAX_5, t0_.DHFIN AS DHFIN_6, t0_.PRIX AS PRIX_7, t0_.TYPE_APPEL AS TYPE_APPEL_8, t0_.PAYS AS PAYS_9, t1_.ID AS ID_10, t1_.DHTRANS AS DHTRANS_11, t1_.MONTANT AS MONTANT_12, t0_.ID_CITOYENS AS ID_CITOYENS_13, t0_.ID_ZONES AS ID_ZONES_14, t0_.STATUT AS STATUT_15, t0_.ID_TRANSACTIONS AS ID_TRANSACTIONS_16, t1_.ID_CITOYENS AS ID_CITOYENS_17, t1_.STATUT AS STATUT_18, t1_.locality_id AS locality_id_19 |
FROM TICKETS t0_ LEFT JOIN TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID |
INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1); |
MariaDB:
+----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+-------------+ |
| 1 | SIMPLE | z2_ | ref | PRIMARY,IDX_729E73D8AAFE1877 | IDX_729E73D8AAFE1877 | 4 | const | 14 | Using index | |
| 1 | SIMPLE | t0_ | ref | IDX_6B0363EEAAD06922 | IDX_6B0363EEAAD06922 | 4 | call2park.z2_.ID | 49471 | |
|
| 1 | SIMPLE | t1_ | eq_ref | PRIMARY | PRIMARY | 4 | call2park.t0_.ID_TRANSACTIONS | 1 | Using where | |
+----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+-------------+ |
Mysql:
+----+-------------+-------+------------+--------+------------------------------+----------------------+---------+-------------------------------+--------+----------+-------------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+-------+------------+--------+------------------------------+----------------------+---------+-------------------------------+--------+----------+-------------+ |
| 1 | SIMPLE | z2_ | NULL | ref | PRIMARY,IDX_729E73D8AAFE1877 | IDX_729E73D8AAFE1877 | 4 | const | 12 | 100.00 | Using index | |
| 1 | SIMPLE | t0_ | NULL | ref | IDX_6B0363EEAAD06922 | IDX_6B0363EEAAD06922 | 4 | CALL2PARK.z2_.ID | 109321 | 100.00 | NULL | |
| 1 | SIMPLE | t1_ | NULL | eq_ref | PRIMARY | PRIMARY | 4 | CALL2PARK.t0_.ID_TRANSACTIONS | 1 | 100.00 | NULL | |
+----+-------------+-------+------------+--------+------------------------------+----------------------+---------+-------------------------------+--------+----------+-------------+ |
--source include/have_sequence.inc
|
--source include/have_innodb.inc
|
|
|
CREATE TABLE t0 ( id_t0 int PRIMARY KEY, id_t1 int, id_t2 int, d datetime, |
KEY (id_t2), KEY (id_t1),KEY (d)) engine=innodb; |
CREATE TABLE t1 (id int PRIMARY KEY) engine=innodb; |
CREATE TABLE t2 (id int PRIMARY KEY, a1 int, KEY (a1))engine=innodb; |
|
|
INSERT INTO t0 SELECT seq,seq,1,now() FROM seq_1_to_50000; |
INSERT INTO t1 SELECT seq FROM seq_1_to_50000; |
INSERT INTO t2 SELECT seq,1 FROM seq_1_to_50000; |
|
analyze SELECT SQL_NO_CACHE t0.id_t0 , t1.id |
FROM t0 |
LEFT JOIN t1 ON t0.id_t1 = t1.id |
JOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1) |
ORDER BY t0.d |
LIMIT 10 ;
|
|
|
analyze format=json SELECT SQL_NO_CACHE t0.id_t0 , t1.id |
FROM t0 |
LEFT JOIN t1 ON t0.id_t1 = t1.id |
JOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1) |
ORDER BY t0.d |
LIMIT 10 ;
|
analyze SELECT SQL_NO_CACHE t0.id_t0 , t1.id
|
FROM t0
|
LEFT JOIN t1 ON t0.id_t1 = t1.id
|
JOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)
|
ORDER BY t0.d
|
LIMIT 10 ;
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
1 SIMPLE t2 ref PRIMARY,a1 a1 5 const 25318 50000.00 100.00 100.00 Using index; Using temporary; Using filesort
|
1 SIMPLE t0 ref id_t2 id_t2 5 test.t2.id 1 1.00 100.00 100.00
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.id_t1 1 1.00 100.00 100.00 Using where; Using index
|
analyze format=json SELECT SQL_NO_CACHE t0.id_t0 , t1.id
|
FROM t0
|
LEFT JOIN t1 ON t0.id_t1 = t1.id
|
JOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)
|
ORDER BY t0.d
|
LIMIT 10 ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 5731.8,
|
"filesort": {
|
"sort_key": "t0.d",
|
"r_loops": 1,
|
"r_total_time_ms": 12.705,
|
"r_limit": 10,
|
"r_used_priority_queue": true,
|
"r_output_rows": 11,
|
"temporary_table": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "a1"],
|
"key": "a1",
|
"key_length": "5",
|
"used_key_parts": ["a1"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 25318,
|
"r_rows": 50000,
|
"r_total_time_ms": 196.81,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "t0",
|
"access_type": "ref",
|
"possible_keys": ["id_t2"],
|
"key": "id_t2",
|
"key_length": "5",
|
"used_key_parts": ["id_t2"],
|
"ref": ["test.t2.id"],
|
"r_loops": 50000,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 4546.8,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "t1",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.t0.id_t1"],
|
"r_loops": 50000,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 808.79,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(trigcond(t0.id_t1 is not null))",
|
"using_index": true
|
}
|
}
|
}
|
}
|
}
|
With MyISAM instead of Innodb:
|
analyze SELECT SQL_NO_CACHE t0.id_t0 , t1.id
|
FROM t0
|
LEFT JOIN t1 ON t0.id_t1 = t1.id
|
JOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)
|
ORDER BY t0.d
|
LIMIT 10 ;
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
1 SIMPLE t0 index id_t2 d 6 NULL 50000 10.00 100.00 100.00 Using where
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.id_t1 1 1.00 100.00 100.00 Using where; Using index
|
1 SIMPLE t2 eq_ref PRIMARY,a1 PRIMARY 4 test.t0.id_t2 1 1.00 100.00 100.00 Using where
|
The example provided by Alice seem to touch a different issue:
InnoDB may return overly optimistic estimates for records_in_range() calls on low-cardinality columns. This issue is filed as MDEV-17111 (and it affects MySQL, too, btw).
I think the problem here is different. The issue is that the join optimizer ignores the presence of ORDER BY .. LIMIT.
As the last EXPLAIN outputs posted by Thomas show, both MariaDB and MySQL will use the join order of "z2,t0,t1" (which does not allow to use LIMIT to short-cut the query execution).
When ORDER BY-LIMIT clause is present, MariaDB will still use "z2,t0,t1" (and will not short-cut the execution) while MySQL will use "z0,t2,t1", which will allow it to use LIMIT to short-cut the execution.
I am not sure why MySQL changes the query plan (if their join optimization process was modified to take advantage of ORDER BY...LIMIT, we would have noticed). Need to check that.
Playing with MySQL 8.0 using a simple query.
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
create table ten_k (a int);
|
insert into ten_k select A.a+1000*B.a from one_k A, ten B;
|
|
|
create table t1 (a int, b int, filler char(200), key(a));
|
insert into t1 select a,a,a from ten_k;
|
|
|
create table t2 like t1;
|
insert into t2 select * from t1;
|
insert into t2 select a+10000, a+10000, a+10000 from t1;
|
alter table t1 add key(b);
|
alter table t2 add key(b);
|
mysql> explain select * from t1, t2 where t1.a=t2.a;
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | a | NULL | NULL | NULL | 9891 | 100.00 | Using where |
|
| 1 | SIMPLE | t2 | NULL | ref | a | a | 5 | test.t1.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|
Ok the base order is t1-t2. t2 is 2x bigger so this makes sense.
mysql> explain select * from t1, t2 where t1.a=t2.a order by t2.b;
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | a | NULL | NULL | NULL | 9891 | 100.00 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | NULL | ref | a | a | 5 | test.t1.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
adding ORDER BY didn't change the join order.
mysql> explain select * from t1, t2 where t1.a=t2.a order by t2.b limit 10;
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
| 1 | SIMPLE | t2 | NULL | index | a | b | 5 | NULL | 10 | 100.00 | Using where |
|
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | test.t2.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
But adding small LIMIT did! Join order is now t2-t1 which allows to use t2.b
index to short-cut LIMIT execution.
mysql> explain select * from t1, t2 where t1.a=t2.a order by t2.b limit 10000000;
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | a | NULL | NULL | NULL | 9891 | 100.00 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | NULL | ref | a | a | 5 | test.t1.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+----------------------------------------------+
|
and it's actually smart - if I increase the LIMIT, the join order changes to be t1-t2 again.
Let's play with selectivity.
alter table t2 add c int;
|
alter table t2 add key(c);
|
update t2 set c=a;
|
explain select * from t1, t2 where t1.a=t2.a and t2.c>9000 order by t2.b limit 2100;
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
| 1 | SIMPLE | t2 | NULL | index | a,c | b | 5 | NULL | 4200 | 50.00 | Using where |
|
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | test.t2.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-------------+
|
Note t2.filtered=50%. It expects to read 4200 rows. in order to satisfy "LIMIT 2100". ORDER BY optimization takes condition selectivity into account, too.
If there is a range condition on t2.b, it will know it doesn't need to take it into account:
mysql> explain select * from t1, t2 where t1.a=t2.a and t2.b between 10 and 100 order by t2.b limit 100;
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+------------------------------------+
|
| 1 | SIMPLE | t2 | NULL | range | a,b | b | 5 | NULL | 91 | 100.00 | Using index condition; Using where |
|
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | test.t2.a | 1 | 100.00 | NULL |
|
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+------------------------------------+
|
varun, can you find which optimization in MySQL causes this? Did they really get ORDER-BY-LIMIT-aware join optimization? Or this is something else?
Here is the output from the mysql's optimizer trace
1) Without LIMIT
https://gist.github.com/varunraiko/cce109233594faf0ed468193307f8d78
2) With LIMIT
https://gist.github.com/varunraiko/7f22e6adabcc2ac3e9334c9d14a883ef
The cost bases solution for calculating the cost of a join order is different between MYSQL and MariaDB.
But Mysql is able to pick the faster plan only by chance.
So the actual solution to this problem would be to implement MDEV-8306
Closing this as a duplicate of MDEV-8306.
Please attach your .cnf file(s) and provide the output of
Thanks.