Announcement

Announcement Module
Collapse
No announcement yet.

Hot to avoid filesort and temporary tables while using "in clause" with "order by&quo

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

  • Hot to avoid filesort and temporary tables while using "in clause" with "order by&quo

    I have been struggling with these problem for many days. I would be very grateful if someone could help me out.

    have two tables like below:

    1. posts

    Field Type
    --------------------------
    post_id int(11) (primary key)
    title text
    content mediumtext
    story_date datetime


    2. post_tags

    Field Type
    ------------------------------
    post_tag_id bigint(20) (primary key)

    post_id int(11)
    tag_id int(11)
    post_date datetime

    Query:
    --------------------------------
    SELECT posts.post_id
    FROM posts, post_tags
    WHERE posts.post_id = post_tags.post_id
    AND post_tags.tag_id IN ( 3, 1186, 676, 568, 75 )
    ORDER BY post_date DESC

    I experimented with making few indexes but not able to remove the warning - "Using where; Using filesort".

    Can someone help me to find right indexes on my tables so that I could get the data only through indexes (no where, no temporary, no filesort message).

  • #2
    please read this post http://forum.mysqlperformanceblog.com/s/m/2140/?srch=key#msg _2140

    Comment

    Working...
    X