Is using LIMIT and OFFSET in MySQL less expensive than returning the full set of records?

It might be a silly question but I am just curious about what goes on behind the curtains.

If I want to paginate database records I can either use 开发者_StackOverflow社区LIMIT and OFFSET or simply get all the records and extrapolate the ones I want with more code.

I know the second option is absolutely silly, I just want to know if it is more expensive

If I use LIMIT and OFFSET will the database grab just what I ask for, or will internally get all the records matching my query (even hundreds of thousands) and then use internally a starting index (OFFSET) and an ending index (OFFSET + LIMIT) to get the requested subset of records?

I don't even know if I used the right words to describe the doubt I have, I hope someone can shed some light.

Thanks!

---

Yes, it would be more expensive, for two reasons.

1) Mysql will optimize internally to only calculate the rows that it needs, rather than retrieving them all internally. Note that this optimization is a lot less if you have an order by in your query, because then mysql has to match and sort all of the rows in the dataset, rather than stopping when it finds the first X in your limit.

2) When all the records are returned, they all need to be transmitted over the wire from the database to your application server. That can take time, especially for medium to large data sets.

---

The difference can be enormous. Not only is the network difference big sometimes (a few rows vs hundreds to thousands), but the number of rows the database needs to find can be large also. For example, if you ask for 10 rows, the database can stop after finding 10 rows, rather than having to check every row.

Whenever possible, use LIMIT and OFFSET.