1

Topic: Difference in the plan for SELECT and DELETE

The same request, it is simple select it is replaced on delete. And to the optimizer takes down a roof.

EXPLAIN EXTENDED
select *
FROM b_sale_basket_props
WHERE
BASKET_ID IN (
SELECT ID
FROM b_sale_basket
WHERE
FUSER_ID IN (
SELECT b_sale_fuser.id
FROM b_sale_fuser
WHERE
b_sale_fuser. DATE_UPDATE <' 2017-12-13 0:13:22 PM'
AND b_sale_fuser. USER_ID IS NULL
) AND ORDER_ID IS NULL
)
LIMIT 2000;
EXPLAIN EXTENDED
delete
FROM b_sale_basket_props
WHERE
BASKET_ID IN (
SELECT ID
FROM b_sale_basket
WHERE
FUSER_ID IN (
SELECT b_sale_fuser.id
FROM b_sale_fuser
WHERE
b_sale_fuser. DATE_UPDATE <' 2017-12-13 0:13:22 PM'
AND b_sale_fuser. USER_ID IS NULL
) AND ORDER_ID IS NULL
)
LIMIT 2000;

2

Re: Difference in the plan for SELECT and DELETE

!

3

Re: Difference in the plan for SELECT and DELETE

Mysql 5.7.19-17