Hi,
* What's the output of these
- select count(distinct usr_id) from tags
- select count(*) from tags
- select count(*) from base
* Does it help to run ANALYZE TABLE on all tables?
I have a feeling that it's possible to optimize the database schema and/or the query fundamentally, but I don't really understand what it's supposed to model so it's hard to suggest something. For example, what's the point with the hash, and do you risk self-joining with rows in base that has the same hash but a different associated usr_id? From what I understand you are interested in usr_ids tagged with x, y and z? If so: what about something like this?
SELECT tags1.name,count(*),tags1.tag_id
FROM tags tags1 JOIN
tags tags2 ON tags1.usr_id=tags2.usr_id JOIN
WHERE tags1.usr_id=4 AND
tags1.tag_id=75 AND
tags2.tag_id=52 AND
GROUP BY tags1.tag_id
If that's not what you're trying to do you'll have to explain in more detail.
Btw, the base.tag_id index can be deleted since it covers the same as tag_hash does.
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com