# Help optimizing a query not to use filesort

**URL:** <https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870>\
**Category:** Other MySQL® Questions\
**Created:** [August 6, 2008, 7:45am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870 "2008-08-06T07:45:37Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bigpun](https://avatars.discourse-cdn.com/v4/letter/b/f04885/32.png) [@bigpun](https://forums.percona.com/u/bigpun)\
**Post date:** [August 6, 2008, 7:45am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870/1 "2008-08-06T07:45:37Z")

</div>

Hi,

The query i’m using is:

SELECT changes.user\_id, changes.changename, changes.date, changes.link, changes.changetype  
FROM changes, friends  
WHERE (friends.user\_id=SOMEID AND friends.user\_id=changes.user\_id) OR (friends.user\_id2=SOMEID AND friends.user\_id2=changes.user\_id) AND changes.date\>SOMEDATE  
GROUP BY changes.change\_id  
ORDER BY changes.date DESC  
LIMIT 0, 10

This query will list a set of changes made by a user, or one of their listed friends (sort of like a mini-feed in Facebook).

When I run an explain on the query I get the output shown in the file attached.

The table ‘changes’ currently has around 180,000 rows (60,000 additional rows get added a month on average - table is 3 months old). So the table is very large, but this is the nature of the table and what it’s meant to do. That’s why im using the date to limit how much is fetched.

The iamge attached also has the table structures

---

<div class="post-metadata">

**Author:** ![bigpun](https://avatars.discourse-cdn.com/v4/letter/b/f04885/32.png) [@bigpun](https://forums.percona.com/u/bigpun)\
**Post date:** [August 11, 2008, 3:22am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870/2 "2008-08-11T03:22:30Z")

</div>

bump!

---

<div class="post-metadata">

**Author:** ![csmiesing](https://avatars.discourse-cdn.com/v4/letter/c/3e96dc/32.png) [@csmiesing](https://forums.percona.com/u/csmiesing)\
**Post date:** [August 11, 2008, 8:39am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870/3 "2008-08-11T08:39:46Z")

</div>

Two questions:  
What version of mysql are you using?  
Why not use the date type for date and use an index on that date?

---

<div class="post-metadata">

**Author:** ![bigpun](https://avatars.discourse-cdn.com/v4/letter/b/f04885/32.png) [@bigpun](https://forums.percona.com/u/bigpun)\
**Post date:** [August 11, 2008, 8:56am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870/4 "2008-08-11T08:56:55Z")

</div>

Mysql version 5.0.45  
Date format isn’t being used as I use linux timestamps - don’t all developers use this nowadays? :-/

---

<div class="post-metadata">

**Author:** ![csmiesing](https://avatars.discourse-cdn.com/v4/letter/c/3e96dc/32.png) [@csmiesing](https://forums.percona.com/u/csmiesing)\
**Post date:** [August 11, 2008, 9:15am UTC](https://forums.percona.com/t/help-optimizing-a-query-not-to-use-filesort/870/5 "2008-08-11T09:15:36Z")

</div>

I always use date as date makes my sql easier to read.  
There is no index on date  
could an combined index on user\_id and date help?

What about:

SELECT changes.user\_id, changes.changename, changes.date, changes.link, changes.changetype  
FROM changes, friends  
WHERE changes.user\_id=SOMEID and ((friends.user\_id=changes.user\_id) OR (friends.user\_id2=changes.user\_id)) AND changes.date\>SOMEDATE  
GROUP BY changes.change\_id  
ORDER BY changes.date DESC