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

**URL:** <https://forums.percona.com/t/hot-to-avoid-filesort-and-temporary-tables-while-using-in-clause-with-order-by-quo/581>\
**Category:** Other MySQL® Questions\
**Created:** [December 29, 2007, 7:26am UTC](https://forums.percona.com/t/hot-to-avoid-filesort-and-temporary-tables-while-using-in-clause-with-order-by-quo/581 "2007-12-29T07:26:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![raghubirthakur](https://avatars.discourse-cdn.com/v4/letter/r/839c29/32.png) [@raghubirthakur](https://forums.percona.com/u/raghubirthakur)\
**Post date:** [December 29, 2007, 7:26am UTC](https://forums.percona.com/t/hot-to-avoid-filesort-and-temporary-tables-while-using-in-clause-with-order-by-quo/581/1 "2007-12-29T07:26:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fmpwizard](https://avatars.discourse-cdn.com/v4/letter/f/e47774/32.png) [@fmpwizard](https://forums.percona.com/u/fmpwizard)\
**Post date:** [December 31, 2007, 10:42am UTC](https://forums.percona.com/t/hot-to-avoid-filesort-and-temporary-tables-while-using-in-clause-with-order-by-quo/581/2 "2007-12-31T10:42:43Z")

</div>

please read this post [http://forum.mysqlperformanceblog.com/s/m/2140/?srch=key#msg](http://forum.mysqlperformanceblog.com/s/m/2140/?srch=key#msg) \_2140