CODEWITHSUNDEEP
mysqlsubquerywhere-in
ademers
ademers

Reputation: 967

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.

Upvotes: 0

Views: 259

Answers (3)

Ankit Sharma
Ankit Sharma

Reputation: 4069

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?

Upvotes: -1

Eugen Rieck
Eugen Rieck

Reputation: 65342

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
;

Upvotes: 1

Jarosław Gomułka
Jarosław Gomułka

Reputation: 4995

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

?

Upvotes: 2

Related Questions