StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy does mysql decide that this subquery is dependent?
text
Body
copied!<p>On a MySQL 5.1.34 server, I have the following perplexing situation:</p> <pre><code>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) </code></pre> <ul> <li>How can it take 3 minutes to examine 500000 records when it only takes 2 minutes to visit all records? </li> <li>How can a subquery on a separate database be classified dependent?</li> <li>What can I do to speed up this query?</li> </ul> <p>UPDATE:</p> <p>The actual query that took a long time was a DELETE, but you can't do explain on those; DELETE is why I used subselect. I have now read the documentation and found out about the syntax "DELETE FROM t USING ..." Rewriting the query from:</p> <pre><code>DELETE FROM master.ObjectValue WHERE timestamp < '2008-06-26 11:21:59' AND id IN ( SELECT id FROM backup.ObjectValue ) ; </code></pre> <p>into:</p> <pre><code>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'; </code></pre> <p>Reduced the time from minutes to .01 seconds for an empty backup.ObjectValue. </p> <p>Thank you all for good advise.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload