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

selecting wrong index

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0(EOL)
    • N/A
    • OTHER
    • None

    Description

      hi guys, mariadb-10.0.12 is selecting the wrong index in some cases:

      the difference from both selects is FORCE INDEX (rendimento)
      this one give better results since "lote_rendimento_data" (column) have less values then (item_id,item_id_red) columns

      i don't know if it's a big problem, but this give some slower results, running optimize don't help, running analyze don't help too, i'm not using histograms

      explain
      		 
      SELECT DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov AS a force index (rendimento), estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3;

      this one takes 1,997 seconds

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary
      1 PRIMARY a range rendimento rendimento 4   171754 Using where
      1 PRIMARY d eq_ref PRIMARY,NewIndex PRIMARY 265 19_org.a.estoque_entrada_org,const 1 Using index condition; Using where
      1 PRIMARY b eq_ref PRIMARY,id,plano_conta_numero,cod_busca id 16 const,19_org.a.item_id_red 1 Using index condition; Using where
      4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition
      3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition
      2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 

      explain
      		 
      SELECT DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov AS a, estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3

      this one takes 2:38 minutes

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary
      1 PRIMARY b range PRIMARY,id,plano_conta_numero,cod_busca cod_busca 265   53 Using index condition
      1 PRIMARY a ref cfop,item,transferencias,rendimento,estoque,giro item 6 const,19_org.b.plano_conta_id_red 3316 Using where
      1 PRIMARY d eq_ref PRIMARY,NewIndex PRIMARY 265 19_org.a.estoque_entrada_org,const 1 Using index condition; Using where
      4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition
      3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition
      2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            rspadim roberto spadim
            Votes:
            1 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.