0

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: explain results

  • Use EXPLAIN on the query and add the results to your question. – jkavalik Sep 27 '15 at 20:11
  • Just added a picture with the results. – Michael van de Rijt Sep 27 '15 at 20:16
  • You are not using actual fulltext index in your query, so it has to scan all tags and try to match them (it is not possible to usefully index the GROUP_CONCAT() results). I am not sure if the fulltext match can do "OR", but even if not, you might do a bit of denormalization by concatenating the texts for all tags to some column linked to the image and then fulltext index that. With your current scheme you probably should get all tags matching either "justin" or "backgrounds" so you limit the inner query and only then the GROUP_CONCAT() will "precise-filter" the remains. – jkavalik Sep 27 '15 at 20:42
0

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.

  • You are miss small "k" after 700 :) – a_vlad Nov 29 '16 at 2:58
0

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

  • do not check the question date ;) – a_vlad Nov 29 '16 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.