MYSQL performance slow using filesort

Question

I have a simple mysql query, but when I have a lot of records (currently 103,0000), the performance is really slow and it says it is using filesort, im not sure if this is why it is slow. Has anyone any suggestions to speed it up? or stop it using filesort?

MYSQL query :

SELECT *    
FROM adverts
WHERE (price >= 0)
    AND (status = 1)
    AND (approved = 1)
ORDER BY date_updated DESC 
LIMIT 19990, 10

The Explain results :

id   select_type   table   type    possible_keys    key    key_len    ref    rows   Extra 
1    SIMPLE        adverts range   price            price  4          NULL   103854 Using where; Using filesort

Here is the adverts table and indexes:

CREATE TABLE `adverts` (
  `advert_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `type_id` tinyint(1) NOT NULL,
  `breed_id` int(10) NOT NULL,
  `advert_type` tinyint(1) NOT NULL,
  `headline` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `price` int(4) NOT NULL,
  `postcode` varchar(7) NOT NULL,
  `town` varchar(60) NOT NULL,
  `county` varchar(60) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `telephone1` varchar(15) NOT NULL,
  `telephone2` varchar(15) NOT NULL,
  `email` varchar(80) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiry_date` datetime NOT NULL,
  PRIMARY KEY (`advert_id`),
  KEY `price` (`price`),
  KEY `user` (`user_id`),
  KEY `type_breed` (`type_id`,`breed_id`),
  KEY `headline_keywords` (`headline`),
  KEY `date_updated` (`date_updated`),
  KEY `type_status_approved` (`advert_type`,`status`,`approved`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Answer 1

The problem is that MySQL only uses one index when executing the query. If you add a new index that uses the 3 fields in your WHERE clause, it will find the rows faster.

ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);

According to the MySQL documentation ORDER BY Optimization:

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:
The key used to fetch the rows is not the same as the one used in the ORDER BY.

This is what happens in your case. As the output of EXPLAIN tells us, the optimizer uses the key price to find the rows. However, the ORDER BY is on the field date_updated which does not belong to the key price.

To find the rows faster AND sort the rows faster, you need to add an index that contains all the fields used in the WHERE and in the ORDER BY clauses:

ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);

The field used for sorting must be in the last position in the index. It is useless to include price in the index, because the condition used in the query will return a range of values.

If EXPLAIN still shows that it is using filesort, you may try forcing MySQL to use an index you choose:

SELECT adverts.*
FROM adverts
FORCE INDEX(status_approved_date_updated)
WHERE price >= 0
AND adverts.status = 1
AND adverts.approved = 1
ORDER BY date_updated DESC 
LIMIT 19990, 10

It is usually not necessary to force an index, because the MySQL optimizer most often does the correct choice. But sometimes it makes a bad choice, or not the best choice. You will need to run some tests to see if it improves performance or not.

Answer 2

Remove the ticks around the '0' - it currently may prevent using the index but I am not sure. Nevertheless it is better style since price is int type and not a character column.

SELECT adverts .*    
FROM adverts
WHERE (
price >= 0
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10