Slow mysql query full text search tag database

Question

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:

Answer 1

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

Answer 2

You should denormalize the tables as such a normalization is leading to too many inner joins and multiple nested select statements.

If normalization is very necessary try maintaining another table. As the number of images is low (~700), you might just skip the image_to_tag table.

Otherwise, if the number of tags per image is very low (3-4), try keeping them all in one field separated with some symbol, e.g. ;, as then they can be easily used by the PHP explode function.