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 i.id = 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,