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

Rodarte

Is it also slow if you omit your LIMIT 19990,10 clause? If you switch that out for LIMIT 10 you may get a much faster operation. In that case you might want to figure out how to use SELECT rather than LIMIT to retrieve those rows. – Baccy

Ive just add the index status_approved (status,approved,date_updated). The explain now says its Using where instead of filesort and the query is taking 0.04 seconds instead of 2 seconds + as it was before, i think this may have solved it. – Rodarte

Just an unrelated thought... none of those parentheses are necessary. – Laurynlausanne

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.

Weakminded

Ive done that now thanks, it hasnt really speeded it up much, and it is still using the filesort. Is there any way to use the index instead of a filesort? – Rodarte

Based on your explain output, it is using the "price" index. I think your DESC sort is causing part of the problem. – Hypercorrection

@user1052096: can you post the EXPLAIN output of the query after creating the index in my answer? – Weakminded

Thanks for pointing me toward FORCE INDEX - that's a lifesaver for the problem I had! – Brainstorming

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

Boccherini

Answer 3

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)

Greeting

Answer 4

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.

Hypercorrection

Answer 5

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.

Phelips