pick = TRUE
reject = TRUE
pick = FALSE
reject = FALSE
SELECT image_tags.tag_name, COUNT(*) as number_of_rows FROM image_tags JOIN images ON image_tags.filename = images.filename WHERE images.pick = FALSE AND images.reject = FALSE GROUP BY image_tags.tag_name ORDER BY number_of_rows
This is your query:
SELECT it.tag_name, COUNT(*) as number_of_rows FROM image_tags it JOIN images i ON it.filename = i.filename WHERE i.pick = FALSE AND i.reject = FALSE GROUP BY it.tag_name ORDER BY number_of_rows;
The first thing to try is an index on
images(pick, reject, filename). This probably won't help, because boolean columns don't usually whittle down the data enough. But it is worth a try.
The second thing to try is this query:
SELECT it.tag_name, (SELECT COUNT(*) FROM images i WHERE it.filename = i.filename AND i.pick = FALSE AND i.reject = FALSE ) as number_of_rows FROM image_tags it ORDER BY number_of_rows;
This moves the aggregation to a subquery, which sometimes performs better in MySQL. For this, you want an index on
images(filename, pick, reject). Also note that this returns all tags, not just those that have a matching image. You can filter using
HAVING number_of_rows > 0 if you want the same results as your version.