I have a table with a little over 3 million rows. Most queries are OK, but when they return a lot of rows they can get a little slow. Example:

SELECT postdate, username, comment
FROM tableA
WHERE id =12345
ORDER BY postdate DESC 
LIMIT 20
OFFSET 0

ID 12345 has 36,580 rows returned, and this query can take up to 20 seconds! Not every time, mind you, but this is a common query that I see in the slow query log (with different IDs, but it's always IDs with a large number of rows returned). Since I can't make it run slow on command, though, it's a bit difficult to run tests and see what makes it faster.

The slow query log showed:

# Query_time: 14.658983 Lock_time: 0.000054 Rows_sent: 20 Rows_examined: 36597

"Explain" gives me:

id            1
select_type     SIMPLE
table          tableA
type          ref
possible_keys  View
key           View
key_len       3
ref           const
rows          17163
Extra         Using where; Using filesort

The "View" index has the columns "id" and "postdate". ID is mediumint, and postdate is bigint(14) that looks like 20160125061814 (1/25/16, 6:18:14am).

FWIW, OFFSET is a variable assigned by the script. The script only shows 20 rows at a time, and clicking "Next" increments OFFSET by 20.

Changing "LIMIT 20" to "LIMIT 0, 20", and then eliminating OFFSET altogether, had no impact.

Can you guys suggest a way to modify either the query or index so that those with a higher number of returns will query faster?

TIA!