Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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?
Assigned to Oleksandr Byelkin to check whether there is a problem in there, or is it something that's not expected to happen.