GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

join query optimization

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • join query optimization

    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.

    Comment

    Working...
    X