[#MCOL-1150] Query performance on MariaDB 10.2 vs MySQL 5.7

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.

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        |

+----+--------------------+-------------------+------------+-------+---------------+--------------+---------+-------------------------------+---------+----------+-------------+

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 |             |

+------+--------------------+-------------------+-------+---------------+--------------+---------+------+---------+-------------+

[MCOL-1150] Query performance on MariaDB 10.2 vs MySQL 5.7 Created: 2018-01-05 Updated: 2021-01-15
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

[MCOL-1150] Query performance on MariaDB 10.2 vs MySQL 5.7 Created: 2018-01-05 Updated: 2021-01-15