How to select N records from a table in mysql

You should have an ORDER BY clause when you use LIMIT, so that you will get the same recordset if you call it two times in succession and no data has changed.

So, do something like:

select  name, cost 
from test 
order by rowid
limit 10; 

To select the first ten records you can use LIMIT followed by the number of records you need:

SELECT name, cost FROM test LIMIT 10

To select ten records from a specific location, you can use LIMIT 10, 100

SELECT name, cost FROM test LIMIT 100, 10

This will display records 101-110

SELECT name, cost FROM test LIMIT 10, 100

This will display records 11-111

To make sure you retrieve the correct results, make sure you ORDER BY the results too, otherwise the returned rows may be random-ish

You can read more @ http://php.about.com/od/mysqlcommands/g/Limit_sql.htm