maria-discuss team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello,

On Thu, Apr 06, 2017 at 01:51:09PM -0500, Sales wrote:
> 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

I take the EXPLAIN output is from 10.1?  The plan is to do a full table scan on
Product_Inventory_Summed, and the optimizer expects to read just one row. How 
many rows does Product_Inventory_Summed table actually have?

(It would be nice to get an EXPLAIN from 5.5, but this is an UPDATE statement, and
EXPLAIN UPDATE is not supported in 5.5. Can you run a SELECT variant on 5.5:

explain 
select 
  * ,
  (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
  )
from Praoduct_Master pm;


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

Just to clarify: are you using "Engine indepdent table statistics", like 
documented here:
https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/

or just innodb_stats_persistent=ON?

what values do you have for @@optimizer_use_condition_selectivity and
@@use_stat_tables?

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

Shouldn't be happening. Let's continue to discuss and find out.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog