How to remove this filesort?

Question

I have problem, my slow query is still using filesort. I can not get rid of this extra flag. Could you please help me?

My query looks like this:

select `User`,
       `Friend` 
from `friends` 
WHERE 
     (`User`='3053741' || `Friend`='3053741') 
     AND `Status`='1' 
ORDER by `Recent` DESC;

Explain says this:

http://blindr.eu/stack/1.jpg

My table structure and indexed (it is in slovak language, but it should be the same):

http://blindr.eu/stack/2.jpg

A little help is needed, how to get rid of this filesort?

Answer 1

USING FILESORT does not mean, that MySQL is actually using a file. The naming is a bit "unlucky". The sorting is done in memory, as long as the data fits into memory. That said, have a look at the variable sort_buffer_size. But keep in mind, that this is a per session variable. Every connection to your server allocates this memory.

The other option is of course, to do the sorting on application level. You have no LIMIT clause anyway.

Oh, and you have too many indexes. Combined indexes can also be used when just the left-most column is used. So some of your indexes are redundant.

If performance is really that much of an issue, try another approach, cause the OR in the WHERE makes it hard, if not impossible, to get rid of using filesort via indexes. Try this query:

SELECT * FROM (
    select "this is a user" AS friend_or_user, `User`, Recent
    from `friends` 
    WHERE 
         `User`='3053741' 
         AND `Status`='1' 

    UNION ALL

    select "this is a friend", 
           `Friend`, Recent
    from `friends` 
    WHERE 
         `Friend`='3053741'
         AND `Status`='1' 
) here_needs_to_be_an_alias
ORDER by `Recent` DESC