Re: Slow simple query in 4000000 records table

[Rick James]

Regardless of FORCE INDEX or not, your query has to step over 4023950 rows to get the 50 you want. The "step over" may involve scanning the table, scanning an index (in your case), or scanning a temp table (if an ORDER BY needs a sort).

Skipping over 4M rows takes time. Period.

If, on the other hand, you pre-compute some flavor of sequence number, and index it, then you might be able to efficiently use that index. But not with OFFSET/LIMIT syntax:

id INT UNSIGNED NOT NULL PRIMARY KEY(id)
SELECT ... WHERE id BETWEEN 4023950 AND 4023950 + 50 - 1;

Caveat: If you ever delete rows, then BETWEEN and OFFSET will give you different rows.

Do you really have a use case for fetching page #80,478?

If it is the "last" page, then ORDER BY DESC LIMIT 0,50 would be immensely faster.