CODEWITHSUNDEEP
phpmysqlsearchtaxonomytagging
Jimmy T.
Jimmy T.

Reputation: 4190

Searching objects by multiple tags in minimal time

I want to search objects by tags. The first approach I came up with was the following model:

Object(OID, Tags, ...)
Tag(TagID, Name, ...)
ObjectTag(OID, TagId)

The field Tags contains all tag names.

Now I don't only want to search by one tag but by many tags. The query looked like this:

SELECT `o`.*
FROM Object o
  INNER JOIN ObjectTag tag1 ON(o.OID = tag1.OID)
  INNER JOIN ObjectTag tag2 ON(o.OID = tag2.OID)
  ...
  INNER JOIN ObjectTag tagn ON(o.OID = tagn.OID)
WHERE tag1.TagID = ?  AND tag2.TagID = ? AND ... AND tagn.TagID = ?

I tested this with about 100k objects and 5-20 tags per object and it took about 30 seconds or more so it often exceeded the PHP time limit.

Out of curiosity I also tried to query based on the field tags using LIKE.

SELECT `o`.*
FROM object
WHERE object.tags LIKE '%tag1%' AND object.tags LIKE = '%tag2%' ...

However, this query was executed in under 10 seconds. Somehow I can understand that scanning one table is faster than an intersect operation but that way I cannot use an index.

My aim is a response time which does not scare the users away.

Upvotes: 0

Views: 118

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

I assume your indexes are optimized for the first example.

Instead of your first query having all of those joins, did you try to join once then make sure every record has that tag. Notice that the HAVING clause's N value is based on the number of tags your are searching for.

SELECT object.keyfield, COUNT(*)
FROM object
JOIN objectTag
  ON object.OID = objectTag.OID
WHERE objectTag.TagID IN (tag1,tag2,tag3...tagN)
GROUP BY object.keyfield
HAVING COUNT(*) >= N

You did not give any sample data with an expected result set. You also did not explicitly show the SELECT clause so this is a best guess.

Upvotes: 1

Related Questions