How To Reduce Number of Rows Scanned by MySQL

Question

I have a query that pulls 5 records from a table of ~10,000. The order clause isn't covered by an index, but the where clause is.

The query scans about 7,700 rows to pull these 5 results, and that seems like a bit much. I understand, though, that the complexity of the ordering criteria complicates matters. How, if at all, can i reduce the number of rows scanned?

The query looks like this:

SELECT *
FROM `mediatypes_article`
WHERE `mediatypes_article`.`is_published` = 1
ORDER BY `mediatypes_article`.`published_date` DESC, `mediatypes_article`.`ordering` ASC,    `mediatypes_article`.`id` DESC LIMIT 5;

medaitypes_article.is_published is indexed.

Answer 1

How many rows apply to "is_published = 1" ? I assume that is like... 7.700 rows?

Either way you take it, the full result that will match the WHERE clause has to be fetched and completely ordered by all sorting criteria. Then the full list of all sorted published articles will be truncated after the first 5 results.

Maybe it will help you to look at the MySQL documentation article about ORDER BY optimization, but for the first you should try to apply indices on the columns that are stated in the ORDER BY statement. It is very likely that this will speed up things greatly.

Answer 2

Executing OPTIMIZE TABLE may not help, but it doesn't hurt either.

Answer 3

When you have ordering, you have to traverse all the btree to figure out the proper order.

10,000 records to order is not that big amount to worry about. Remember, with proper indexing, the RDBMS doesn't fetch the whole record to figure out the order. It has the indexed columns in btree pages saved on disk and with few page reads, the whole btree is loaded in memory and can be traversed.

Answer 4

In MySQL you can make an index that includes multiple columns. I think what you probably need to do is make an index that includes is_published and published_date. You should look at the output from the EXPLAIN statement to make sure it's doing things the smart way, and add an index if it is not.