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 adverts .*    
FROM adverts
WHERE (
price >='0'
)
AND (
adverts.status = 1
)
AND (
adverts.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 AUTO_INCREMENT=103878 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

Your WHERE condition uses price, status, approved to select, and then date_updated is used to sort.

So you need a single index with those fields; I'd suggest indexing on approved, status, price and date_updated, in this order.

The general rule is placing WHERE equalities first, then ranges (more than, less or equal, between, etc), and sorting fields last. (Note that leaving one field out might make the index less usable, or even unusable, for this purpose).

CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);

This way, access to the table data is only needed after LIMIT has worked its magic, and you will slow-retrieve only a small number of records.

I'd also remove any unneeded indexes, which would speed up INSERTs and UPDATEs.

Answer 3

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

Answer 4

MySQL does not make use of the key date_updated for the sorting but just uses the price key as it is used in the WHERE clause. You could try to to use index hints:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Add something like

USE KEY FOR ORDER BY  (date_updated)

Answer 5

I have two suggestions. First, remove the quotes around the zero in your where clause. That line should be:

price >= 0

Second, create this index:

CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);

This should allow MySQL to find the 10 rows specified by your LIMIT clause by using only the index. Filesort itself is not a bad thing... the number of rows that need to be processed is.