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

subquery don't materialize

    XMLWordPrintable

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

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            rspadim roberto spadim
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.