Slow mysql query full text search tag database 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 image_tag table.

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)


Table: image

id | filename 
1  | image1.jpg
2  | image2.jpg
3  | image3.jpg


Table: image_to_tag

image_id | image_tag_id
1        | 1
1        | 2
2        | 3
2        | 4


Table: image_tag

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.

Results explain query:
as I understand work of FT - it must be prepared in advance, so command

WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)


against derived table:

(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


must not work - just because when index created no this table present

because query work (not return error from Your description) - MySQL ignore this, but need test with full table structure

also, JOIN with derived table - do not want to use index