[MDEV-6921] poorly optimized UPDATE/DELETE ()=() with primary key Created: 2014-10-24  Updated: 2016-04-19

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: roberto spadim Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: None


 Description   

hi guys, a poorly optimized UPDATE/DELETE (with select it's ok )

explain

UPDATE spamov_itens SET cur_oe=(
 
  SELECT cur_oe FROM spamov WHERE (unidade_id,lote_tipo,lote_spa)=(1009,'v',105248)
 
),cur_oe_seq=(
 
  SELECT cur_oe_seq FROM spamov WHERE (unidade_id,lote_tipo,lote_spa)=(1009,'v',105248)
 
) 
WHERE (unidade_id,lote_tipo,lote_spa)=(1009,'v',105248)

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY spamov_itens ALL         248896 Using where
3 SUBQUERY spamov const PRIMARY,spamov_unidade_id,spamov_oe,spamov_op,estorno,orc PRIMARY 14 const,const,const 1  
2 SUBQUERY spamov const PRIMARY,spamov_unidade_id,spamov_oe,spamov_op,estorno,orc PRIMARY 14 const,const,const 1  

the not optimized part is the update where (the last where)

WHERE (unidade_id,lote_tipo,lote_spa)=(1009,'v',105248)
 
 
 
change to:
 
WHERE unidade_id=1009 and lote_tipo='v' and lote_spa=105248

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY spamov_itens range PRIMARY,spamov_itens_op,spamov_itens_simula_carga,spamov_itens_unidade_id,spamov_itens_itens PRIMARY 14   10 Using where
3 SUBQUERY spamov const PRIMARY,spamov_unidade_id,spamov_oe,spamov_op,estorno,orc PRIMARY 14 const,const,const 1  
2 SUBQUERY spamov const PRIMARY,spamov_unidade_id,spamov_oe,spamov_op,estorno,orc PRIMARY 14 const,const,const 1  

with DELETE i have:
DELETE FROM xxxx WHERE (primary key)=(consts) -> not optimized
DELETE xxxx FROM xxxx WHERE (primary key)=(consts) -> this one is optimized

 Comments   
Comment by Elena Stepanova [ 2014-10-24 ]

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

Comment by roberto spadim [ 2014-11-04 ]

i'm testing another query now with aria engine + partition: 

UPDATE table1 SET some_non_index_file=some_non_index_file
 
WHERE (one_field_primary_key) IN (select primary_key_field from other_table)

without using the partition field

query:

update rastreabilidade as a, t1 as b set 
a.pbruto=a.pliq,a.pbruto_real=a.pliq
 
where a.codigo_Barra=b.mov_id

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL PRIMARY       7345921  
1 SIMPLE b eq_ref PRIMARY PRIMARY 30 19_org.a.codigo_barra 1 Using where; Using index

query:

update rastreabilidade set pbruto=pliq,pbruto_real=pliq
 
where codigo_Barra in (select mov_id from t1)

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY rastreabilidade ALL         7345921 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 30 func 1 Using index; Using where

this query expend 1minute
select count from t1 => 1500 rows

CREATE TABLE `t1` (
 
  `mov_id` char(30) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`mov_id`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 
 
CREATE TABLE `rastreabilidade` (
 
  `codigo_barra` char(20) NOT NULL DEFAULT '',
 
  table_partition int not null default 0,
 
  pliq_real int not null,
 
  pliq int not null,
 
  pbruto_real int not null,
 
  pbruto int not null,
 
  PRIMARY KEY (`codigo_barra`,`table_partition`),
 
) ENGINE=Aria DEFAULT CHARSET=latin1
 
PARTITION BY RANGE (table_partition)
 
(PARTITION p0 VALUES LESS THAN (1),
 
 PARTITION p1 VALUES LESS THAN MAXVALUE)

maybe this could help?

Comment by Oleksandr Byelkin [ 2016-04-19 ]

In any case it can't be considered as a bug, but feature request for 10.3 or upper (IMHO)

Generated at Thu Feb 08 07:15:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.