maria-discuss team mailing list archive

[Sales <info@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>]

We are noticing many dependent subqueries not working as well as they did on Mariadb 5.5, we’re having to add a force index.

Relatively simple example:

explain update Product_Master pm set Total_Inventory = (select sum(ifnull(pi.Quantity_On_Hand, pi.Other_Quantity)) from Product_Inventory_Summed pi where pi.Product_ID = pm.Product_ID group by pi.Product_ID);
+------+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | PRIMARY            | pm    | index | NULL          | PRIMARY | 22      | NULL | 5001829 |             |
|    2 | DEPENDENT SUBQUERY | pi    | ALL   | PRIMARY       | NULL    | NULL    | NULL |       1 | Using where |
+------+--------------------+-------+-------+---------------+---------+---------+------+---------+——————+

This query runs in a couple of minutes on 5.5, runs for many many hours on 10.1

By adding the force index, it is back to “normal”. 

explain update Product_Master pm set Total_Inventory = (select sum(ifnull(pi.Quantity_On_Hand, pi.Other_Quantity)) from Product_Inventory_Summed pi force index (PRIMARY) where pi.Product_ID = pm.Product_ID group by pi.Product_ID);
+------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+-------+
| id   | select_type        | table | type  | possible_keys | key     | key_len | ref                 | rows    | Extra |
+------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+-------+
|    1 | PRIMARY            | pm    | index | NULL          | PRIMARY | 22      | NULL                | 5001829 |       |
|    2 | DEPENDENT SUBQUERY | pi    | ref   | PRIMARY       | PRIMARY | 22      | feeds.pm.Product_ID |       1 |       |
+------+--------------------+-------+-------+---------------+---------+---------+---------------------+---------+———+

We are using persistent statistics on both tables, so, it knows there are a lot of rows in each. I don’t see any optimizer switches that are turned off. 

Any hints as to why or is this a current limitation of 10.1?

Steve