Hi Roberto,
Is it reproducible on 10.0.14?
i will upgrade the database at weekend 
but probably yes, i checked with a replicate server (10.0.14) here, but not at production, next week i will check
i executed a analyze at production but this don't help, at weekend i will get a new position
i will update at weekend, if not work, i will check if patch of MDEV-7118 make some good result, i'm not sure if it's running a loose scan or something like it instead of use the right index (it's a normal, not unique, index)
from what i look at patch (mdev-7118) - (just to don't loose the information)
=== modified file 'sql/opt_range.cc'
— a/sql/opt_range.cc 2014-10-28 21:33:31 +0000
+++ b/sql/opt_range.cc 2014-11-19 14:14:49 +0000
@@ -12799,11 +12799,11 @@ get_best_group_min_max(PARAM *param, SEL
uint cur_used_key_parts;
/*
- Check (B1) - if current index is covering. Exclude UNIQUE indexes, because
- loose scan may still be chosen for them due to imperfect cost calculations.
+ Check (B1) - if current index is covering.
+ (was also: "Exclude UNIQUE indexes ..." but this was removed because
+ there are cases Loose Scan over a multi-part index is useful).
*/ - if (!table->covering_keys.is_set(cur_index) ||
- cur_index_info->flags & HA_NOSAME)
+ if (!table->covering_keys.is_set(cur_index))
goto next_index;
/*
@@ -12942,6 +12942,16 @@ get_best_group_min_max(PARAM *param, SEL
}
/*
+ Aplly a heuristic: there is no point to use loose index scan when we're
+ using the whole unique index.
+ */
+ if (cur_index_info->flags & HA_NOSAME &&
+ cur_group_key_parts == cur_index_info->user_defined_key_parts)
+
+
+ /*
Check (NGA1, NGA2) and extract a sequence of constants to be used as part
of all search keys.
*/
table analyzed... 10.0.14 ok 
i will take more tests at friday (many update/insert/delete) and check if optimizer don't get poor performace again
well, not many update/delete/insert and ...
/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 00:08:13 */
8 minutes...
same wrong index
| 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 | 49 | Using index condition | |
| 1 | PRIMARY | a | ref | cfop,item,transferencias,rendimento,estoque,giro | item | 6 | const,19_org.b.plano_conta_id_red | 3347 | 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 |
status (com_*)
updates = 100
inserts = 14
deletes = 21
some variables:
optimizer_prune_level=1
optimizer_search_depth=62
optimizer_selectivity_sampling_limit=100
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
optimizer_use_condition_selectivity=1
histogram_size=0
histogram_type=SINGLE_PREC_HB
use_stat_tables=NEVER
using "set @@optimizer_use_condition_selectivity=5;"
reduce from 8 minutes to 6 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 | b | range | PRIMARY,id,plano_conta_numero,cod_busca | cod_busca | 265 | 49 | Using index condition | |
| 1 | PRIMARY | a | ref | cfop,item,transferencias,rendimento,estoque,giro | item | 6 | const,19_org.b.plano_conta_id_red | 3347 | Using where |
| 1 | PRIMARY | d | ALL | PRIMARY,NewIndex | 7749 | Using where; Using join buffer (flat, BNL join) | |||
| 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 |
using "set @@optimizer_use_condition_selectivity=5;"
and FORCE INDEX (rendimento)
reduce from 1,997 seconds to 0,252 seconds
well getting back to testing (upgraded to mariadb-10.0.15 from MDEV-7182) i will check if optimizer is selecting the right index
well, maybe we still have problems....
set @@optimizer_use_condition_selectivity = 5;
|
explain
|
SELECT sql_no_cache 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
|
8 minutes 04 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 | b | range | PRIMARY,id,plano_conta_numero,cod_busca | cod_busca | 265 | 48 | Using index condition | |
| 1 | PRIMARY | a | ref | cfop,item,transferencias,rendimento,estoque,giro | item | 6 | const,19_org.b.plano_conta_id_red | 3347 | Using where |
| 1 | PRIMARY | d | ALL | PRIMARY,NewIndex | 7749 | Using where; Using join buffer (flat, BNL join) | |||
| 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 |
—
set @@optimizer_use_condition_selectivity = 1;
19 seconds first time, 6 seconds 2nd,3rd,4...10 time
—
set @@optimizer_use_condition_selectivity = 5;
8 minutes 04 seconds first time, 6 seconds 2nd,3rd,4...10 time
—
using FORCE INDEX(rendimento)
set @@optimizer_use_condition_selectivity = 5;
2.339 seconds first time, 0,203 2nd,3rd,4...10 time
—
set @@optimizer_use_condition_selectivity = 1;
0,515 seconds first time, 0,17 2nd,3rd,4...10 time
====
for the first time we have a big time, ok no buffers/cache, seconds and others have buffer/cache and execute faster
explain with FORCE INDEX:
| 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 | 238077 | 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 |
—
maybe something is prefering a more complex search instead of a single index search
any space to upload private data available?
it's ftp://ftp.askmonty.org, log in as anonymous and it's possible to upload the data which will not be publicly visible. Please mention the file name here so we can find it
i start dump now, with some hours i will upload as mdev-7125.sql.tgz
i changed table est_mov to est_mov_myisam (from replica database)
any news i will comment here again
thanks
should i use:
ftp://ftp.askmonty.org/private/
or
ftp://ftp.askmonty.org/public/
or
ftp://ftp.askmonty.org/secret/
?
tested with private, i'm compatcing and sending file
Experimenting on 10.0.14 for starters.
My results on the initial query for further comparison (I only count "warm" results):
|
Forced index: 0.22 sec |
SELECT DISTINCT "GOOD" 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_myisam 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-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3; |
+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ |
| 1 | PRIMARY | c | const | PRIMARY,NewIndex | PRIMARY | 265 | const,const | 1 | 100.00 | Using temporary | |
| 1 | PRIMARY | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | |
| 1 | PRIMARY | d | eq_ref | PRIMARY,NewIndex | PRIMARY | 265 | test.a.estoque_entrada_org,const | 1 | 100.00 | Using index condition; Using where | |
| 1 | PRIMARY | b | eq_ref | PRIMARY,id,plano_conta_numero,cod_busca | id | 16 | const,test.a.item_id_red | 1 | 100.00 | Using index condition; Using where | |
| 4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
| 3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
| 2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ |
|
No index: 3.28 sec |
SELECT DISTINCT "BAD" 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_myisam 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-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3; |
+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+ |
| 1 | PRIMARY | c | const | PRIMARY,NewIndex | PRIMARY | 265 | const,const | 1 | 100.00 | Using temporary | |
| 1 | PRIMARY | b | range | PRIMARY,id,plano_conta_numero,cod_busca | cod_busca | 265 | NULL | 42 | 76.19 | Using index condition | |
| 1 | PRIMARY | a | ref | cfop,item,transferencias,rendimento,estoque,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | |
| 1 | PRIMARY | d | eq_ref | PRIMARY,NewIndex | PRIMARY | 265 | test.a.estoque_entrada_org,const | 1 | 100.00 | Using index condition; Using where | |
| 4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
| 3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
| 2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | |
+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+ |
Still on 10.0.14.
Simplified query (it takes much longer on the same data, which means we can possibly reduce the data and still see the performance difference):
|
Forced index: 1 min 20 sec |
SELECT COUNT(*) AS GOOD FROM est_mov_myisam a force index(rendimento), estoque_itens AS b, cfop_itens AS c |
WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' ; |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | |
| 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 280 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 8818 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
|
No index: interrupted after 26 min of execution |
SELECT COUNT(*) AS BAD FROM est_mov_myisam a, estoque_itens AS b, cfop_itens AS c |
WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' ; |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 8818 | 100.00 | Using index | |
| 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | |
| 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 280 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
The number of rows in tables 'b' and 'c' is not important for reproducing the difference, only for the absolute execution time.
Below `t2` is the same as `estoque_itens`, and `t3` is the same as `cfop_itens`, only both `t2` and `t3` have 2 rows each:
|
Forced index: 0.28 sec |
SELECT COUNT(*) AS GOOD FROM est_mov_myisam a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | |
| 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 2 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ |
|
No index: 2.68 sec |
SELECT COUNT(*) AS BAD FROM est_mov_myisam a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using index | |
| 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | |
| 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 2 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ |
Partitioning on est_mov_myisam is not important. Below `t1` is the same as `est_mov_myisam` (which is, btw, an Aria table!), but without partitioning.
|
Forced index: 0.21 sec |
SELECT COUNT(*) AS GOOD FROM t1 a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
|
No index: 2.87 sec |
SELECT COUNT(*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
yeap data engine didn't change optimizer
i got as aria/myisam from a replicated server
i tryed with innodb from master and same problem, i think it's related to some optimizer cost function selecting the "item" index instead of "rendimento" index, i don't know if optimizer prefer primary key columns is it possible?
i will include a new mdev to explain better why optimizer select one index instead of another (MDEV-7239)
Switched to 10.0.15 (release tag). Same results
MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1 a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+ |
| GOOD |
|
+------+ |
| 0 |
|
+------+ |
1 row in set (0.24 sec) |
{code:sql}
|
MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 0 |
|
+-----+ |
1 row in set (2.78 sec) |
@elena
i upgrade from a mariadb 10.0.12 (or 10.0.5, i don't remember) and got this problem
maybe trying an older release we can find what commit cause this problem?
i will include a new mdev to explain better why optimizer select one index instead of another (
MDEV-7239)
rspadim,
Please don't generate multiple JIRA issues about the same problem. If you want to add more details about the failure you have already filed, use comments. If you want to share general thoughts about what might be wrong with optimizer, please email to the mailing lists.
hi elena, no problem, it's not about this mdev, it's about a new optimizer 'feature' (i didn't found this in mysql/mariadb) got this idea now from this mdev cause i was executing explain ... force index ( first index), force index (second index) .... to get information about the optimizer, i will contact maria-discuss/developer to check if this is a relevant mdev or not
about this mdev(7125) this problem happen after a mariadb upgrade (from 10.0.5 or 10.0.12 i don't remember now) maybe we can find the commit that cause this bug getting back to 10.0.5 and check what changed?
Depends on who you mean by "we"
If you want, you can do it, of course.
With the bug filed this way, I can't afford it.
Loading this dump on a previous (unknown) version and going up revision by revision through 700 revisions is extremely time-consuming, it isn't worth it.
Bisecting would be even more time-consuming because I would have to load dump over and over again (cannot really downgrade the datadir and expect it to be a clean experiment).
i didn't checked how many revisions was done before thinking about it
hum could i help with this anyway?
could i help with this anyway?
Will request information if necessary, no worries.
ok, i tested with innodb and got the same index choise
Back to business...
Switched all tables to MyISAM. Numbers are slightly different, but the problem is still present:
MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam AS a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 0 |
|
+-----+ |
1 row in set (1.95 sec) |
|
|
MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+ |
| GOOD |
|
+------+ |
| 0 |
|
+------+ |
1 row in set (0.25 sec) |
elena, i don't know where is json explain of mariadb development, do you know where i could download (even not yet fully implemented) just to check if json explain could help here i think it's one git 10.1 tree, but i don't know what branch/commit
Roberto, when/if we need more input, we will request it, you can count on it. For now, there is no need for you to do anything. Thanks.
Simplified a bit more (removed the third table):
MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 863 |
|
+-----+ |
1 row in set (1.98 sec) |
|
|
+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using index | |
| 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 5355 | 100.00 | Using index condition; Using where | |
+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+ |
| GOOD |
|
+------+ |
| 863 |
|
+------+ |
1 row in set (0.26 sec) |
|
|
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+ |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 47677 | 75.00 | Using index condition; Using where | |
| 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+ |
The structure of table b and extra indexes on table a are unimportant:
MariaDB [test]> show create table t2_min \G |
*************************** 1. row ***************************
|
Table: t2_min |
Create Table: CREATE TABLE `t2_min` ( |
`plano_conta_id_red` bigint(20) NOT NULL DEFAULT '0' |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec) |
Also removed all indexes from t1_myisam except for rendimento and item.
MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+ |
| GOOD |
|
+------+ |
| 863 |
|
+------+ |
1 row in set (0.29 sec) |
|
|
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 40418 | 75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------- |
MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 863 |
|
+-----+ |
1 row in set (1.94 sec) |
|
|
+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | SIMPLE | a | ref | item,rendimento | item | 6 | const,test.b.plano_conta_id_red | 5355 | 100.00 | Using index condition; Using where | |
+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
Presence of index 'item' is important. If there is no 'item', 'rendimento' is picked voluntarily, without forcing:
MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 863 |
|
+-----+ |
1 row in set (0.24 sec) |
|
|
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 40418 | 75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
i didn't tested... item_id/item_id_red columns are part of primary key, if you remove primary key and leave item and rendimento index, optimizer prefer item index?
from "show index from est_mov_myisam":
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| est_mov_myisam | 1 | rendimento | 1 | rendimento | A | 3 | BTREE | |||||
| est_mov_myisam | 1 | rendimento | 2 | lote_rendimento_data | A | 6078 | BTREE | |||||
| est_mov_myisam | 1 | rendimento | 3 | item_id_red | A | 832974 | BTREE | |||||
| est_mov_myisam | 1 | item | 1 | item_id | A | 10 | BTREE | |||||
| est_mov_myisam | 1 | item | 2 | item_id_red | A | 8786 | BTREE | |||||
| est_mov_myisam | 1 | item | 3 | data_estoque | A | 14577050 | BTREE |
As requested, results (no need to go through previous comments, they are intermediate steps).
- The problem is that the query uses ref access with the index `item` on the table `a`, while range with index `rendimento` provides significantly better performance for this query. It is reproducible. Depending on the query, performance difference varied from 1.5x to 30x and more (in some cases I didn't wait for the "bad" query to finish).
- The query is simplified (see below) to leave only one join and only AND conditions. No subqueries. Also, all select items have been replaced by a single COUNT to make the result set nicer.
- The table structures are simplified to only have columns and indexes that directly participate in the query. Also, tables are switched from Aria to MyISAM, and partitioning is removed.
- The data for the second table is reduced to 2 rows.
- The data for the first table (the one with indexes in question) is shortened a bit, to 20 mln rows (the original data was 29 mln rows). It's also possible to reduce it to some 15 mln and still have the difference in plans, but the performance difference becomes negligible
At this point, the performance difference on my machine is ~3x (0.13 vs 0.35 sec). On another machine ~4x (0.07 vs 0.30 sec). This is for "warm" queries, cold ones can take considerably longer and are less predictable.
Below the "BAD" query is the one where the wrong index/access is used. The "GOOD" query is where the desired index is enforced.
MariaDB [test]> select count(*) from t1_min; |
+----------+ |
| count(*) | |
+----------+ |
| 20000000 |
|
+----------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> select count(*) from t2_min; |
+----------+ |
| count(*) | |
+----------+ |
| 2 |
|
+----------+ |
1 row in set (0.00 sec) |
CREATE TABLE `t1_min` ( |
`item_id` mediumint(8) unsigned NOT NULL DEFAULT '0', |
`item_id_red` mediumint(9) NOT NULL DEFAULT '0', |
`lote_rendimento_data` date NOT NULL DEFAULT '0000-00-00', |
`rendimento` enum('Y','N') NOT NULL DEFAULT 'N', |
KEY `item` (`item_id`,`item_id_red`), |
KEY `rendimento` (`rendimento`,`lote_rendimento_data`,`item_id_red`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
|
|
CREATE TABLE `t2_min` ( |
`plano_conta_id_red` bigint(20) NOT NULL DEFAULT '0' |
) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
MariaDB [test]> show index in t1_min; |
+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| t1_min | 1 | item | 1 | item_id | A | 5 | NULL | NULL | | BTREE | | | |
| t1_min | 1 | item | 2 | item_id_red | A | 4672 | NULL | NULL | | BTREE | | | |
| t1_min | 1 | rendimento | 1 | rendimento | A | 2 | NULL | NULL | | BTREE | | | |
| t1_min | 1 | rendimento | 2 | lote_rendimento_data | A | 3518 | NULL | NULL | | BTREE | | | |
| t1_min | 1 | rendimento | 3 | item_id_red | A | 338983 | NULL | NULL | | BTREE | | | |
+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
SELECT COUNT(*) AS GOOD FROM t1_min a force index(rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+------+ |
| GOOD |
|
+------+ |
| 863 |
|
+------+ |
1 row in set (0.13 sec) |
|
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 52529 | 75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) | |
|
|
| Note | 1003 | select count(0) AS `GOOD` from `test`.`t1_min` `a` FORCE INDEX (`rendimento`) join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y') and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13') and (`test`.`a`.`lote_rendimento_data` < '2014-10-17')) | |
SELECT COUNT(*) AS BAD FROM t1_min a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y'; |
+-----+ |
| BAD |
|
+-----+ |
| 863 |
|
+-----+ |
1 row in set (0.35 sec) |
|
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | SIMPLE | a | ref | item,rendimento | item | 6 | const,test.b.plano_conta_id_red | 4280 | 100.00 | Using index condition; Using where | |
|
|
| Note | 1003 | select count(0) AS `BAD` from `test`.`t1_min` `a` join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y') and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13') and (`test`.`a`.`lote_rendimento_data` < '2014-10-17')) | |
I've uploaded the new dump to ftp.askmonty.org/private/mdev7125-smaller.dump.gz.
hi guys any news?
10.0 was EOLed in March 2019