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.

• mysql
• subquery
• where-in

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
;

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?