MYSQL performance is slow using filesort

Question

I have a simple mysql query, but when I have a lot of records (currently 103,0000), the performance is very slow and it says it uses filesort, im not sure why that is why it is slow. Anyone have suggestions for speeding it up? or stop it with 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 

Explanation 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 a table of declarations 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 uses only one index when executing a query. If you add a new index that uses 3 fields in the WHERE , it will find rows faster.

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

According to MySQL ORDER BY Optimization documentation:

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

This is what happens in your case. As EXPLAIN output shows us, the optimizer uses the price key to search for strings. However, ORDER BY is in the date_updated field, which does not belong to the price key.

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

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

The field used for sorting should be at 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 can try getting MySQL to use the index of your choice:

 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 

Usually there is no need to force the index, because the MySQL optimizer most often makes the right choice. But sometimes it makes a bad choice, or not a good choice. You will need to run some tests to make sure that it improves performance or not.

Answer 2

Uncheck '0' - this may currently interfere with the use of the index, but I'm not sure. However, this is a better style since the price is int , not a column of characters.

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

Answer 3

I have two suggestions. First, remove the quotation marks around zero in the where section. This 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 in your LIMIT clause using only the index. Filesort itself is not so bad ... the number of lines that need to be processed is.

Answer 4

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

So, you need single indexes with these fields; I would suggest indexing on approved , status , price and date_updated in that order.

In a general rule, WHERE equalities are first set, then ranges (greater, less or equal, between, etc.) and the last sort fields. (Note that leaving one field may make the index less useful or even unsuitable for this purpose).

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

Thus, access to the table data is necessary only after LIMIT worked its magic, and you will slowly retrieve only a small number of records.

I also remove unnecessary indexes that speed up INSERT and UPDATE s.

Answer 5

MySQL does not use the date_updated key to sort, but simply uses the price key, as it is used in the WHERE . You can try using the indicative hints:

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

Add something like

 USE KEY FOR ORDER BY (date_updated)