ademers ademers - 5 months ago 16
MySQL Question

Why does this simple query take forever?

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.

Answer

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
;