Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
CentOS Linux release 7.2.1511 (Core)
Description
Below is the query which runs in less than 1.5 min in MySQL 5.7,but on MariaDB it is running more than 5 minutes.
On checking the explain plan MySQL using an index on derived table but MariaDB doesn't.
Note : In MySQL we have 71L records and in MariaDB we have 72L. Both having same structure and index.
Explain on MySQL
UPDATE test.FUID SET AND_FTD=(select and_ftd from (SELECT MOBILE, MIN(CASE WHEN BRAND_NEW IN ('AND') THEN BILLDATE END ) and_ftd FROM HISTORY GROUP BY MOBILE ) a where FUID.MOBILE=a.MOBILE);
|
|
|
+----+--------------------+-------------------+------------+-------+---------------+--------------+---------+-------------------------------+---------+----------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+--------------------+-------------------+------------+-------+---------------+--------------+---------+-------------------------------+---------+----------+-------------+
|
| 1 | UPDATE | FUID | NULL | ALL | NULL | NULL | NULL | NULL | 1078188 | 100.00 | NULL |
|
| 2 | DEPENDENT SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 23 | test.FUID.MOBILE| 715905 | 100.00 | Using where |*
|
| 3 | DERIVED | HISTORY | NULL | index | MOBILE | MOBILE | 23 | NULL | 7159059 | 100.00 | NULL |
|
+----+--------------------+-------------------+------------+-------+---------------+--------------+---------+-------------------------------+---------+----------+-------------+
|
|
On MariaDB
UPDATE test.FUID SET AND_FTD=(select and_ftd from (SELECT MOBILE, MIN(CASE WHEN BRAND_NEW IN ('AND') THEN BILLDATE END ) and_ftd FROM HISTORY GROUP BY MOBILE ) a where FUID.MOBILE=a.MOBILE);
|
|
|
+------+--------------------+-------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
| 1 | PRIMARY | FUID | ALL | NULL | NULL | NULL | NULL | 1120281 | |
|
| 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 7028102 | Using where |*
|
| 3 | DERIVED | HISTORY | index | NULL | MOBILE | 23 | NULL | 7028102 | |
|
+------+--------------------+-------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
|