Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6920

subquery don't materialize

        Details

          Description

          Hi guys, i have a subquery that don't materialize:

          SELECT 
a.plano_conta_id,a.plano_conta_id_red,
(
	SELECT SUM(quant) FROM est_atu WHERE 
	unidade_id=1001 AND item_id=a.plano_conta_id AND item_id_red=a.plano_conta_id_red
) AS est, 0 as est_ext
FROM estoque_itens AS a 
WHERE 
a.plano_conta_id=67 AND a.habilitado='Y' AND grupo_itens='N'  AND 
((a.plano_conta_id=67 AND a.plano_conta_numero LIKE "%") )  AND 
a.plano_conta_id_red IN (
	SELECT plano_conta_id_red FROM lista_preco WHERE 
	distribuidor_tipo='j' AND distribuidor_id=1 AND plano_conta_id=67
)


HAVING est>0 OR est_ext>0 
ORDER BY a.plano_conta_id,a.codigo_busca LIMIT 30 OFFSET 0
          id select_type table type possible_keys key key_len ref rows Extra
          1 PRIMARY lista_preco ref PRIMARY,item item 2 const 3577 Using where; Using index; LooseScan; Using temporary; Using filesort
          1 PRIMARY a eq_ref PRIMARY,id,plano_conta_numero id 16 const,19_org.lista_preco.plano_conta_id_red 1 Using index condition; Using where
          2 DEPENDENT SUBQUERY est_atu ALL PRIMARY       5640 Using where

          this part:

          a.plano_conta_id_red IN (
	SELECT plano_conta_id_red FROM lista_preco WHERE 
	distribuidor_tipo='j' AND distribuidor_id=1 AND plano_conta_id=67
)

          return 1300 rows
          if I 

          SET @@session.group_concat_max_len = 1024000;
SELECT GROUP_CONCAT(plano_conta_id_red)  FROM lista_preco WHERE distribuidor_tipo='j' AND distribuidor_id=1 AND plano_conta_id=67;

          and change the IN (SELECT), to IN (number,number,number,number)
          the query drop from: 2 seconds to 0.1 second

          my doubt is, why the subquery was not materialized?

          my optimizer_switch:

          index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,*materialization=on*,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

          check: materialization=on, semijoin=on

          some numbers: (all small tables)

          select count(*) from lista_preco => 9338 rows;
select count(*) from est_atu => 5640 rows;
select count(*) from estoque_itens => 8788 rows;

          maybe could we include a SQL_MATERIALIZE or something like it to execute the temporary create table and use it (materialize the subquery) ?

          something like:

          SELECT 
a.plano_conta_id,a.plano_conta_id_red,
(
	SELECT SUM(quant) FROM est_atu WHERE 
	unidade_id=1001 AND item_id=a.plano_conta_id AND item_id_red=a.plano_conta_id_red
) AS est, 0 as est_ext
FROM estoque_itens AS a 
WHERE 
a.plano_conta_id=67 AND a.habilitado='Y' AND grupo_itens='N'  AND 
((a.plano_conta_id=67 AND a.plano_conta_numero LIKE "%") )  AND 
a.plano_conta_id_red IN (
	SELECT SQL_MATERIALIZE plano_conta_id_red FROM lista_preco WHERE 
	distribuidor_tipo='j' AND distribuidor_id=1 AND plano_conta_id=67
)


HAVING est>0 OR est_ext>0 
ORDER BY a.plano_conta_id,a.codigo_busca LIMIT 30 OFFSET 0

          today my work around without create table or without group concat is:

          SELECT 
a.plano_conta_id,a.plano_conta_id_red,
(
	SELECT SUM(quant) FROM est_atu WHERE 
	unidade_id=1001 AND item_id=a.plano_conta_id AND item_id_red=a.plano_conta_id_red
) AS est, 0 as est_ext
FROM estoque_itens AS a 
WHERE 
(a.plano_conta_id,a.plano_conta_id_red) IN (
	SELECT c.plano_conta_id,c.plano_conta_id_red FROM lista_preco AS b,estoque_itens AS c WHERE 
	c.plano_conta_id=67 AND c.habilitado='Y' AND c.grupo_itens='N'  AND 
	((c.plano_conta_id=67 AND c.plano_conta_numero LIKE "%") )  AND 
	b.plano_conta_id=c.plano_conta_id AND
	b.plano_conta_id_red=c.plano_conta_id_red and
	b.distribuidor_tipo='j' AND b.distribuidor_id=1 AND b.plano_conta_id=67
)


HAVING est>0 OR est_ext>0 
ORDER BY a.plano_conta_id,a.codigo_busca LIMIT 30 OFFSET 0
          id select_type table type possible_keys key key_len ref rows Extra
          1 PRIMARY a ref PRIMARY,id,plano_conta_numero PRIMARY 8 const 1769 Using where
          1 PRIMARY c eq_ref PRIMARY,id,plano_conta_numero id 16 const,19_org.a.plano_conta_id_red 1 Using where
          1 PRIMARY b ref PRIMARY,item item 10 const,19_org.a.plano_conta_id_red,const,const 1 Using where; Using index; FirstMatch(c)
          2 DEPENDENT SUBQUERY est_atu ALL PRIMARY       5640 Using where

          but that's 'very ugly', don't use materialized optimizer at explain, and use 4 tables instead of 3, i don't know if we have a problem with big unions using more tables, or if we are using the most optimized query

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Assigned to Sergei Petrunia ] to check whether there is a problem in there, or is it something that's not expected to happen.

                Show
                elenst Elena Stepanova added a comment - Assigned to Sergei Petrunia ] to check whether there is a problem in there, or is it something that's not expected to happen.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    rspadim roberto spadim
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated: