[thelist] a question on MYSQL LIMIT clause - large tables.

Juha Suni juha.suni at sparecom.fi
Thu Feb 16 09:57:34 CST 2006

> SELECT columns From tableName WHERE id > 9000 LIMIT 30
> OR
> SELECT columns From tableName LIMIT 30  OFFSET 9000

Actually I've usually just used the standard LIMIT syntax of
SELECT columns From tableName LIMIT 9000,30

which is easy for pagination.

I just did a very quick test with not that big of a database, and my query 
seemed to perform equally to your second method (using OFFSET). As you 
thought, your first query (WHERE id>9000) was actually the fastest, but I 
would not draw any big conclusions without a bigger test. id field was 
indexed, too, if that matters.


More information about the thelist mailing list