StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POA subquery that should be independent is not. Why?
text
Body
copied!<p>I have a table <code>files</code> with files and a table <code>reades</code> with read accesses to these files. In the table <code>reades</code> there is a column <code>file_id</code> where refers to the respective column in <code>files</code>.</p> <p>Now I would like to list all files which have not been accessed and tried this:</p> <pre><code>SELECT * FROM files WHERE file_id NOT IN (SELECT file_id FROM reades) </code></pre> <p>This is terribly slow. The reason is that mySQL thinks that the subquery is dependent on the query:</p> <pre><code>+----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | files | ALL | NULL | NULL | NULL | NULL | 1053 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | reades | ALL | NULL | NULL | NULL | NULL | 3242 | 100.00 | Using where | +----+--------------------+--------+------+---------------+------+---------+------+------+----------+-------------+ </code></pre> <p>But why? The subquery is completely independent and more or less just meant to return a list of ids.</p> <p>(To be precise: Each <code>file_id</code> can appear multiple times in <code>reades</code>, of course, as there can be arbitrarily many read operations for each file.)</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