Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.12
-
Ubuntu 18.04, n.a.
Description
5 different queries which should do the same thing but 3 different execution plans:
1st case customer complained (cdrs_process is much smaller than cdrs):
EXPLAIN
|
DELETE FROM cdrs |
WHERE uniqueid IN (SELECT uniqueid FROM cdrs_process) |
;
|
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+ |
| 1 | PRIMARY | cdrs | ALL | NULL | NULL | NULL | NULL | 197418 | Using where | |
| 2 | DEPENDENT SUBQUERY | cdrs_process | unique_subquery | PRIMARY | PRIMARY | 122 | func | 1 | Using index | |
+------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+ |
Can/should the optimizer not do this automatically with the new features?
Rewriting to multi-table delete helped. We have seen some strange behaviour with aliasing but could not reproduce it. The next CEP seems ok:
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp |
WHERE c.uniqueid = cp.uniqueid |
;
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| 1 | SIMPLE | cp | index | PRIMARY | start | 10 | NULL | 300 | Using index | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
EXPLAIN
|
DELETE cdrs |
FROM cdrs JOIN cdrs_process ON cdrs.uniqueid = cdrs_process.uniqueid |
;
|
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| 1 | SIMPLE | cdrs_process | index | PRIMARY | start | 10 | NULL | 300 | Using index | |
| 1 | SIMPLE | cdrs | eq_ref | PRIMARY | PRIMARY | 122 | test.cdrs_process.uniqueid | 1 | | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp ON c.uniqueid = cp.uniqueid |
;
|
|
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| 1 | SIMPLE | cp | index | PRIMARY | start | 10 | NULL | 300 | Using index | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
But this one is a bit less optimal compared to the previous 3:
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp USING(uniqueid) |
;
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+ |
| 1 | SIMPLE | cp | ALL | PRIMARY | NULL | NULL | NULL | 300 | | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+ |
Should be reproducible with the attached test data.
Attachments
Issue Links
- relates to
-
MDEV-22415 Single table UPDATE/DELETE doesn't use non-semijoin Materialization
-
- Closed
-
-
-
- Closed
-
Activity
Most of this should have been fixed in MDEV-7487. I'm not sure about the cause of the difference between the query with = and the query with USING.
lstartseva, could you check this?
Could you also attempt to construct a testcase for MDEV-22415 and see if it is fixed as Igor guessed it would be?
After fix in in MDEV-7487 all 5 queries have the same plan:
EXPLAIN
|
DELETE FROM cdrs |
WHERE uniqueid IN (SELECT uniqueid FROM cdrs_process) |
;
|
|
|
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| 1 | PRIMARY | cdrs_process | index | PRIMARY | PRIMARY | 122 | NULL | 300 | Using index | |
| 1 | PRIMARY | cdrs | eq_ref | PRIMARY | PRIMARY | 122 | test.cdrs_process.uniqueid | 1 | | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp |
WHERE c.uniqueid = cp.uniqueid |
;
|
|
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| 1 | SIMPLE | cp | index | PRIMARY | PRIMARY | 122 | NULL | 300 | Using index | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
EXPLAIN
|
DELETE cdrs |
FROM cdrs JOIN cdrs_process ON cdrs.uniqueid = cdrs_process.uniqueid |
;
|
|
|
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
| 1 | SIMPLE | cdrs_process | index | PRIMARY | PRIMARY | 122 | NULL | 300 | Using index | |
| 1 | SIMPLE | cdrs | eq_ref | PRIMARY | PRIMARY | 122 | test.cdrs_process.uniqueid | 1 | | |
+------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+ |
|
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp ON c.uniqueid = cp.uniqueid |
;
|
|
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| 1 | SIMPLE | cp | index | PRIMARY | PRIMARY | 122 | NULL | 300 | Using index | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
EXPLAIN
|
DELETE c |
FROM cdrs AS c JOIN cdrs_process AS cp USING(uniqueid) |
;
|
|
|
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
| 1 | SIMPLE | cp | index | PRIMARY | PRIMARY | 122 | NULL | 300 | Using index | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 122 | test.cp.uniqueid | 1 | | |
+------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ |
We had the same case again today with an UPDATE statement. Same solution. I was told that MS SQL Server also solves query without problems.