I faced the issue during migration from MariaDB 10.11 to 11.0 version that some specific queries run longer.
During running EXPLAIN output I got this.
MariaDB 10.11
<code>MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | a | range | PRIMARY,FK_articleinfo_id | FK_articleinfo_id | 5 | NULL | 103925 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | Using index |
| 1 | SIMPLE | o | ref | FK_order_customerid,FK_order_articleid | FK_order_articleid | 4 | my_schema.a.ID | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | Using index |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 1 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.a.ID | 13 | Using where; Using index |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.a.ID | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
9 rows in set (0,015 sec)
</code>
<code>MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | a | range | PRIMARY,FK_articleinfo_id | FK_articleinfo_id | 5 | NULL | 103925 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | Using index |
| 1 | SIMPLE | o | ref | FK_order_customerid,FK_order_articleid | FK_order_articleid | 4 | my_schema.a.ID | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | Using index |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 1 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.a.ID | 13 | Using where; Using index |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.a.ID | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
9 rows in set (0,015 sec)
</code>
MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | a | range | PRIMARY,FK_articleinfo_id | FK_articleinfo_id | 5 | NULL | 103925 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | Using index |
| 1 | SIMPLE | o | ref | FK_order_customerid,FK_order_articleid | FK_order_articleid | 4 | my_schema.a.ID | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | Using index |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 1 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.a.ID | 13 | Using where; Using index |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.a.ID | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+--------------------------------------------------------+
9 rows in set (0,015 sec)
MariaDB 11
<code>MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| 1 | SIMPLE | o | ALL | FK_order_customerid,FK_order_articleid | NULL | NULL | NULL | 214315 | Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,FK_articleinfo_id | PRIMARY | 4 | my_schema.o.articleid | 1 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 3 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.o.articleid | 24 | Using where |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.o.articleid | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
</code>
<code>MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| 1 | SIMPLE | o | ALL | FK_order_customerid,FK_order_articleid | NULL | NULL | NULL | 214315 | Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,FK_articleinfo_id | PRIMARY | 4 | my_schema.o.articleid | 1 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 3 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.o.articleid | 24 | Using where |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.o.articleid | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
</code>
MariaDB [my_schema]> EXPLAIN SELECT SQL_NO_CACHE `article_data_view`.`esl` AS `esl` FROM `article_data_view` GROUP BY `article_data_view`.`esl` ORDER BY `article_data_view`.`esl` ASC LIMIT 1000;
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
| 1 | SIMPLE | o | ALL | FK_order_customerid,FK_order_articleid | NULL | NULL | NULL | 214315 | Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,FK_articleinfo_id | PRIMARY | 4 | my_schema.o.articleid | 1 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,FK_cu,id_userid | PRIMARY | 4 | my_schema.o.customerid | 1 | |
| 1 | SIMPLE | ai | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.a.articleinfo_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | my_schema.c.userid | 1 | |
| 1 | SIMPLE | addr | ref | FK_address_customer,Idx_address_addresstype | FK_address_customer | 5 | my_schema.o.customerid | 3 | Using where |
| 1 | SIMPLE | ami | ref | PRIMARY,FK_articlemediafile_fileid | PRIMARY | 4 | my_schema.o.articleid | 24 | Using where |
| 1 | SIMPLE | amf | eq_ref | PRIMARY,filetype,filetype_sortorder | PRIMARY | 4 | my_schema.ami.fileid | 1 | Using where |
| 1 | SIMPLE | api | ref | FK_custom_key_id | FK_custom_key_id | 4 | my_schema.o.articleid | 1 | |
+------+-------------+-------+--------+---------------------------------------------+----------------------------------------+---------+----------------------------+--------+---------------------------------+
As you may notice at version 10 it firstly filters on a table and applies index during select but on 11 it gets all of the records on o table. I am using same dump, same configuration. I also tried to run mysqlcheck on 11th version but it doesn’t help.
Any ideas here? Rewriting legacy queries might not be an option here, I need to fix it with the least intrusion possible.
Thank you in advance.