Thursday, 30 August 2018

Mysql ORDER BY does not use the index when multiple fields are present in SELECT

I'm trying to simplify a query to figure out why it's so slow on the production server. The idea is to grab the X most recent entries for pagination. The problem is, MySQL's optimizer seems to want to use filesort instead of the Primary Key (ID). Stripping away all the extraneous stuff, the following works as desired, using the index (primary):

EXPLAIN SELECT ID FROM table ORDER BY ID DESC

However, these variations resort to filesort:
EXPLAIN SELECT ID, field2 FROM table ORDER BY ID DESC
EXPLAIN SELECT * FROM table ORDER BY ID DESC

I need to return several fields, so that doesn't work... I can get around the problem in the simplified query with:
EXPLAIN SELECT * FROM table FORCE INDEX (Primary) ORDER BY ID DESC

but I haven't figured out how to work that into the larger query with table joins. Am I missing something really simple?

Try this query
select * from table order by id desc limit (pageNO-1) * noEntries , noEntries

eg for page 1 and 10 entries per page
 select * from table order by id desc limit 0, 10

eg for page 2 and 10 entries per page
select * from table order by id desc limit 10, 10

0 comments:

Post a Comment