Why does this MySQL query take forever (and never finishes) on a table that has 17k rows?
SELECT * FROM files_folders WHERE file IN (SELECT file FROM files_folders WHERE folder = 123);
Basically, a file can be in several folders (a physical file and its copies). I'm trying to get all the files in folder 123. Right now in my example there's 2 files in folder 123. ID #4222 & ID #7121. But those 2 files could be in other folders as well as folder 123.
Am I doing this the wrong way or is there something I'm missing?
Edit: Here's an example of the table structure.
+--------------+
| file | folder|
+------+-------+
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
| 3 | 2 |
| 4 | 3 |
+------+-------+
So I want to select all files (and its copies) that are in folder 1 which would return:
+--------------+
| file | folder|
+------+-------+
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
+------+-------+
Because file 1 is both in folder 1 and 2.
Thank you.
SELECT * FROM files_folders WHERE Folder = 123;
Alex Howansky 2012-04-03 19:43
SELECT * FROM files_folders WHERE Folder = 123
? Currently you're selecting the ID
where Folder = 123
and then essentially selecting *
where ID = ID
- David 2012-04-03 19:43
Use a self join:
SELECT
ff.*
FROM
files_folders AS ff
INNER jOIN files_folders AS f ON f.ID=ff.ID
WHERE
f.Folder=123
;
DELETE
. The SELECT works fine but as soon as I incorporate it into a DELETE
statement, it takes forever again. I've replaced the SELECT ff.*
from your query to DELETE ff
. Any thoughts on why it takes a long time? Thanks - ademers 2012-04-03 21:08
IN
function. I can only do a subquery. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_i - ademers 2012-04-04 13:38
SELECT CAST(GROUP_CONCAT(DISTINCT file) AS CHAR) FROM files_folders WHERE folder = 123
- this gives back a string like 1,2,17
. Then run "DELETE FROM files_folders WHERE file in (0" + string_from_last_query+")"
Eugen Rieck 2012-04-04 13:47
For each file, MySQL need to check if ID
is in results returned by subquery. It takes O(N)
.
It need to be done for N
files.
So complexity of your query is O(N^2)
.
17k ^ 2 = ~4*10^8
so it should take around a minute, maybe less.
Why your query isn't
SELECT ID FROM files_folders WHERE Folder = 123
?
Why are you using sub query? I don't think it's needed at all. You can just select directly from table like
SELECT * FROM files_folders WHERE Folder = 123
and a second thing:
"Because a file can be in another folder also"
What does it means to use sub query?
Folder
- Daniel A. White 2012-04-03 19:40