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
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.
--source include/have_sequence.inc--source include/have_innodb.incLIMIT 10 ;LIMIT 10 ;analyze SELECT SQL_NO_CACHE t0.id_t0 , t1.idFROM t0LEFT JOIN t1 ON t0.id_t1 = t1.idJOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)ORDER BY t0.dLIMIT 10 ;id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra1 SIMPLE t2 ref PRIMARY,a1 a1 5 const 25318 50000.00 100.00 100.00 Using index; Using temporary; Using filesort1 SIMPLE t0 ref id_t2 id_t2 5 test.t2.id 1 1.00 100.00 100.001 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.id_t1 1 1.00 100.00 100.00 Using where; Using indexanalyze format=json SELECT SQL_NO_CACHE t0.id_t0 , t1.idFROM t0LEFT JOIN t1 ON t0.id_t1 = t1.idJOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)ORDER BY t0.dLIMIT 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.idFROM t0LEFT JOIN t1 ON t0.id_t1 = t1.idJOIN t2 ON t0.id_t2 = t2.id AND (t2.a1 = 1)ORDER BY t0.dLIMIT 10 ;id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra1 SIMPLE t0 index id_t2 d 6 NULL 50000 10.00 100.00 100.00 Using where1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.id_t1 1 1.00 100.00 100.00 Using where; Using index1 SIMPLE t2 eq_ref PRIMARY,a1 PRIMARY 4 test.t0.id_t2 1 1.00 100.00 100.00 Using where