Optimizing this query.

[altendew]

For someone reason this query runs slower

SELECT * 
FROM 3280296_spends
ORDER BY id
LIMIT 2800 , 200

than this query..

SELECT * 
FROM 3280296_spends
ORDER BY id

There is a primary key on 'id'

 

[dodge20]

How MySQL Optimizes `LIMIT'
---------------------------

In some cases *MySQL* will handle the query differently when you are
using `LIMIT #' and not using `HAVING':

* If you are selecting only a few rows with `LIMIT', *MySQL* will
use indexes in some cases when it normally would prefer to do a
full table scan.

* If you use `LIMIT #' with `ORDER BY', *MySQL* will end the sorting
as soon as it has found the first `#' lines instead of sorting the
whole table.

* When combining `LIMIT #' with `DISTINCT', *MySQL* will stop as
soon as it finds `#' unique rows.

* In some cases a `GROUP BY' can be resolved by reading the key in
order (or do a sort on the key) and then calculate summaries until
the key value changes. In this case `LIMIT #' will not calculate
any unnecessary `GROUP BY''s.

* As soon as *MySQL* has sent the first `#' rows to the client, it
will abort the query.

* `LIMIT 0' will always quickly return an empty set. This is useful
to check the query and to get the column types of the result
columns.

* The size of temporary tables uses the `LIMIT #' to calculate how
much space is needed to resolve the query.

Dodge20