Re: Jet Profiler says I'm slow....

[Rick James]

> SELECT * FROM items WHERE CAT='sports' ORDER BY DATUM DESC LIMIT 31900,20

Begs for a "compound" index:
INDEX(CAT, DATUM)

Even then, it will have to scan 31920 rows to get the answer. Or it may choose to do a full table scan, sort the results (on DATUM), then scan 31920 rows.

OFFSET is the main villain. Why are you doing it? It smells like "pagination". But do you really need to look at page 3191?

If you had PRIMARY KEY(ID), INDEX(CAT, DATUM, ID), then this would work somewhat faster:

SELECT  b.*
    FROM  
      ( SELECT  ID
            FROM  items
            WHERE  CAT='sports'
            ORDER BY  DATUM DESC
            LIMIT  31900,20 ) a
    JOIN  items b ON b.ID = a.ID;

The trick in that is to turn a table scan into an index scan.

Please show us the output of
SHOW CREATE TABLE items \G
SHOW TABLE STATUS LIKE 'items' \G

The real solution to "pagination via offset" is to turn it into remembering where you "left off".