Optimizing Laravel 5 & MySQL for 9M+ rows. Step 1.

Will Bowman
Aug 2, 2016 · 2 min read

Don’t use LengthAwarePaginator. Use Paginator, aka, simplePaginate.

LengthAwarePaginator does a count(*) on your table to generate a total number of rows available.

Certainly LengthAwarePaginator makes for more features but its massively slow, even with my best indexes and a r3.large rds scanning 9M+ rows takes more then a second (and I’ll have 35M+).

Switching over to simplePaginate removed the count and the slow load times along with it.

Why use paginate() anyway?

It’s good for small data sets, in the tens of thousands, but when you reach millions — it’s gotta go.

I decided to switch everything to simplePaginate as almost all of my data sets are large and I had to adjust Fractal to support it, killing off support for LengthAwarePaginator (for now).

Conclusion

Laravel tells us..

If you only need to display simple “Next” and “Previous” links in your pagination view, you may use the simplePaginate method to perform a more efficient query. This is very useful for large datasets when you do not need to display a link for each page number when rendering your view:

I find it easy to forget how much faster simplePaginate() is over paginate() and often find myself using paginate() in new projects (as I did this time).

Once the project grows to millions of rows you’ll be stuck trying to refactor to simplePaginate(), so make life easy, and just use it to begin with.

Update

I realize now that the simplePaginate() is a LIMIT OFFSET method of pagination that also suffers from performance issues every page you go.

This article explained it well.

What’s a good solution? Pass the last id you received and instead of ?page=1 do ?last_id=XXX and select all items where the id is greater then it.

I decided to write my own Paginator for Laravel 5, you can find it here. It will paginate based on the last items id (or whatever field you define).


Will Bowman

Written by

Web Developer Since 1998

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade