Help optimizing a query not to use filesort

MySQL & MariaDB Other MySQL® Questions
1

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

2

bump!

3

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

4

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

5

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