In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. Can someone please help me speed it up, or create another one.
There are indexes on all the id-fields and the name field in the
SELECT image.* FROM image INNER JOIN (SELECT image_to_tag.image_id, GROUP_CONCAT(image_tag.`name`) AS tags FROM image_to_tag INNER JOIN image_tag ON (image_tag.id = image_to_tag.image_tag_id) GROUP BY image_to_tag.image_id) t ON t.image_id = image.id WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)
id | filename 1 | image1.jpg 2 | image2.jpg 3 | image3.jpg
image_id | image_tag_id 1 | 1 1 | 2 2 | 3 2 | 4
id | name 1 | justin bieber 2 | backgrounds 3 | justin timberlake 4 | other backgrounds
If I search for "justin background" I want to find images 1 and 2. If I search for "justin bieber background" I want to find image 1.