Geek asks and answers

Why does mysql decide that this subquery is dependent?

On MySQL 5.1.34 server, I have the following situation:

mysql> explain select * FROM master.ObjectValue WHERE id IN ( SELECT id FROM backup.ObjectValue ) AND timestamp < '2008-04-26 11:21:59';
+----+--------------------+-------------+-----------------+-------------------------------------------------------------+------------------------------------+---------+------+--------+-------------+
| id | select_type        | table       | type            | possible_keys                                               | key                                | key_len | ref  | rows   | Extra       |
+----+--------------------+-------------+-----------------+-------------------------------------------------------------+------------------------------------+---------+------+--------+-------------+
|  1 | PRIMARY            | ObjectValue | range           | IX_ObjectValue_Timestamp,IX_ObjectValue_Timestamp_EventName | IX_ObjectValue_Timestamp_EventName | 9       | NULL | 541944 | Using where | 
|  2 | DEPENDENT SUBQUERY | ObjectValue | unique_subquery | PRIMARY                                                     | PRIMARY                            | 4       | func |      1 | Using index | 
+----+--------------------+-------------+-----------------+-------------------------------------------------------------+------------------------------------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select * FROM master.ObjectValue WHERE id IN ( SELECT id FROM backup.ObjectValue ) AND timestamp < '2008-04-26 11:21:59';
Empty set (2 min 48.79 sec)

mysql> select count(*) FROM master.ObjectValue;
+----------+
| count(*) |
+----------+
| 35928440 |
+----------+
1 row in set (2 min 18.96 sec)
  • How can it take 3 minutes to study 500,000 records, when it only takes 2 minutes to visit all the records?
  • How can a subquery for an individual database be classified as dependent?
  • What can I do to expedite this request?

UPDATE:

The actual query, which took a long time, was DELETE, but you cannot explain it; DELETE is why I used subselect. Now I read the documentation and learned about the syntax "REMOVE FROM USE ..." Overwriting the request from:

DELETE FROM master.ObjectValue 
WHERE timestamp < '2008-06-26 11:21:59' 
AND id IN ( SELECT id FROM backup.ObjectValue ) ;

at

DELETE FROM m 
USING master.ObjectValue m INNER JOIN backup.ObjectValue b ON m.id = b.id 
WHERE m.timestamp < '2008-04-26 11:21:59';

Reduced time from minutes to 0.01 seconds for an empty backup. ObjectValue.

Thanks everyone for the good advice.

+3
source
4

, 1 ? , 1 . , mysql 1 . , mysql, , , "" , , , . , , , .

mysql (), "timestamp <" 2008-04-26 11:21:59 ". Mysql , , , . mysql , . , . , .

, , . .

+3

( , , , ).

, :

SELECT m.*
FROM master.ObjectValue m
JOIN backup.ObjectValue USING (id)
WHERE m.timestamp < '2008-06-26 11:21:59'

MySQL , . - , , . , FROM, .

:

DELETE FROM m WHERE m.rid IN (SELECT id FROM r WHERE r.xid = 10)
// vs
DELETE m FROM m WHERE m.rid IN (SELECT id FROM r WHERE r.xid = 10)

. , .

+5

3 , 500000 , 2 ?

COUNT(*) COUNT(1) MySQL. , , , , , . range (<) IN, , , , , .

?

, , . , , ... , , . , MySQL, - ​​ , .

, ?

JOIN:

SELECT master.*
FROM master.ObjectValue master
JOIN backup.ObjectValue backup
  ON master.id = backup.id
  AND master.timestamp < '2008-04-26 11:21:59';
+3

: MySQL, - . Postgres, .

For everyone who says “use JOIN,” it's just stupid immortalized by the crowd of MySQL, which for 10 years has refused to fix this terribly terrible mistake.

0
source

More articles:


All Articles