Optimizing Laravel 5 & MySQL for 9M+ rows. Step 1.
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.
