join query optimization

MySQL & MariaDB Other MySQL® Questions
1

Hello

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:

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

2

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.