Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

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

raghubirthakurraghubirthakur EntrantInactive User Role Beginner
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).

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.