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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
