Re: help understanding query...

[Nathan Ziarek]

OK...I've figured out the query (that 'ALL' command sure is helpful):

SELECT DISTINCT(keywordid) FROM media.map_keywordimage WHERE imageid=ANY(SELECT imageid FROM media.map_keywordimage WHERE keywordid=33);

That works, but with the tiny amount of data I have now, it take 11 seconds to run.

I've seen some other examples where people have suggested alternative table layouts. Here is mine, in case someone can suggest something.

media.map_keywordimage
+------+---------+-----------+
| id | imageid | keywordid |
+------+---------+-----------+
| 1 | 434 | 7 |
| 2 | 434 | 9 |
| 3 | 434 | 20 |
| 4 | 434 | 39 |
| 5 | 434 | 41 |
| 6 | 436 | 7 |
| 7 | 436 | 9 |
...etc

I also have another table that I do not use that simply matches each keyword with any other keyword it shares an image with:

mysql> SELECT * FROM media.map_keyword;
+----+------------+------------+
| id | keywordid1 | keywordid2 |
+----+------------+------------+
| 1 | 41 | 7 |
| 2 | 7 | 41 |
| 3 | 41 | 9 |
| 4 | 9 | 41 |
| 5 | 41 | 20 |
| 6 | 20 | 41 |
...etc

I originaly thought that, when creating this table (which is truncated and recreated from scratch from another DB), it would be smarter to make the correlations here, rather than when a user requested them on the site. It was ugly, but if it worked, I was all for it. My problem here is that, if a user chooses keyword 7, I want the system to only return keywords that will yeild actual results. If the user then chooses keyword 9, I want the system to only display keywords that work with BOTH 7 AND 9. The error comes in when 7 matches with 10 and 9 matches with 10, but 7, 9 and 10 never appear on the same picture.

OK, I am babbling now.

Is there anything I can do to make that query above faster?