Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.14
-
linux
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