MySql, can anyone suggest how to improve my query / index?

Can anyone suggest how I can improve my query, its used for a site map and theres 8开发者_C百科0000 pages, hence the limit.

Heres my query

SELECT PageName
FROM pads
WHERE RemoveMeDate = '2001-01-01 00:00:00'
ORDER BY PadID DESC
LIMIT 20000 , 30000

Heres my EXPLAIN

Heres the indexes I have already

Optimal index for this query would probably be a combination index on columns RemoveMeDate, PadID, and PageName.

Try not using any indexes. How many rows are there in the table overall?

I notice that you are looking for 10k rows out of about 48k rows matching on just RemoveMeDate - the table must be large or you must be selecting a large proportion of the table.

SELECT PageName
FROM pads USE INDEX()
WHERE RemoveMeDate = '2001-01-01 00:00:00'
ORDER BY PadID DESC
LIMIT 20000 , 30000

Not using any indexes forces it to just scan the table. Now if that doesn't help and is slower, try to make a specific index that orders DESC, e.g. try one of the two below

CREATE INDEX IX_pads_rmd_iddesc ON pads (RemoveMeDate, PadID DESC, PageName)
CREATE INDEX IX_pads_rmd_iddesc ON pads (RemoveMeDate, PadID DESC)

In conjunction with FORCE INDEX in case it doesn't get selected automatically

SELECT PageName
FROM pads USE INDEX(IX_pads_rmd_iddesc)
WHERE RemoveMeDate = '2001-01-01 00:00:00'
ORDER BY PadID DESC
LIMIT 20000 , 30000

Note that even though the indexes re created "PadID DESC", it is ignored by MySQL. One can hope that one day when it gets implemented it will fall into place.

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

you should look into "force index" or "use index" if correct indexes are not being automatically selected.

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