Is it possible to avoid filesort?

Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it.

I tried adding indexes, for both top_expire and program, but didn't help
With the ORDER BY the query takes over 1 second and withoud it is just 0.003 seconds on localhost

Query

SELECT ad.*, p.link
    FROM (SELECT ad.*
        FROM mod_ad ad 
        JOIN mod_ad_auta auta ON ad.id = auta.ad_id
        WHERE ad.active != 0 AND ad.usr_active != 0 AND ad.expire > 1371151608  AND ad.cat_id = '1' AND ad.price <= '10000' 
          AND auta.rocnik BETWEEN '1950' AND '2013' 
          AND auta.km BETWEEN '0' AND '500000'
        ORDER BY top_expire DESC, program DESC,  ad.id DESC  LIMIT 0,10) as ad
JOIN pages p ON ad.page_id=p.page_id;

Schema

``
CREATE TABLE mod_ad (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(10) unsigned NOT NULL,
page_id int(10) unsigned NOT NULL,
cat_id int(10) unsigned NOT NULL,
subcat_id int(10) unsigned NOT NULL,
program tinyint(1) unsigned NOT NULL,
region_id int(10) unsigned NOT NULL,
zone_id int(10) unsigned NOT NULL,
city_id int(10) unsigned NOT NULL,
sort enum('firm','priv') NOT NULL,
type enum('predaj','kúpa','výmena','darujem','hľadám','ponúkam','iné') NOT NULL,
condition varchar(24) NOT NULL,
name varchar(128) NOT NULL,
desc text NOT NULL,
location varchar(128) NOT NULL,
keywords varchar(255) NOT NULL,
price decimal(10,2) NOT NULL,
price_type varchar(20) NOT NULL,
cperson varchar(128) NOT NULL,
firmname varchar(128) NOT NULL,
zip varchar(5) NOT NULL,
email varchar(255) NOT NULL,
tel varchar(20) NOT NULL,
tel2 varchar(20) NOT NULL,
web varchar(255) NOT NULL,
video varchar(255) NOT NULL,
marked_expire int(11) unsigned NOT NULL,
top_expire int(11) unsigned NOT NULL,
ad_hot_expire int(11) unsigned NOT NULL,
ad_border_expire int(11) unsigned NOT NULL,
ad_heading_expire

The docs say


In some cases, MySQL cannot use indexes to resolve the ORDER BY,
although it still uses indexes to find the rows that match the WHERE
clause. These cases include the following:

and then go on to list many circumstances that prevent MySQL from using indexes. Among them are

• You use ORDER BY on different keys

• The key used to fetch the rows is not the same as the one used in the ORDER BY

and there are probably others.

To avoid a filesort, you have to find a way to get what you want without running into any of those (many) documented conditions.

You can also

• Increase the size of the sort_buffer_size variable.

• Increase the size of the read_rnd_buffer_size variable.

• Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them.

• Change tmpdir to point to a dedicated file system with large amounts of free space.

(Same link.)

Also see docs for Optimizing LIMIT Queries, which interacts with ORDER BY.

StackExchange Database Administrators Q#44614, answer score: 17