Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
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