join query optimization


I’m running a db which has this tables:
tags 1400 rows
link_tag_items 595000 rows
items 212000 rows

my query shows the items which are connected to a tag ordered by responses or creation time:

from items i, link_tag_items l
where = l.item_id and l.tag_id=N and l.is_ok = true and i.has_responses = true
order by i.responses DESC LIMIT 10;

the order by secion may be one of: i.responses DESC, i.created_at, i.created_at DESC.

according to the explain mysql uses my index on link_tag_items, and than the PRIMARY index on the items table.
i think the issue is that it first executing the part for the links table and then tries to sort ~10000 records (some tags has more than 10000 items linked to them)

thanks for your help,

You can’t really tell what should be done or what can be optimized from your post. Why not post your create tables and index stats along with the explain.