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

MySQL & MariaDB Other MySQL® Questions
1

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

  1. 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