Laravel pagination - Select Count() query

Question

I'm using Laravel built in paginate method in a query where i need search in Fulltext against a large dataset (about 100K rows with huge amount of text each).

All working fine, except i do not understand the logic in how laravel counts the results: why must execute the same query two times (the select count() as aggregate) for retrieve the total count of results, and not use the php function count(), that works great in this scenario. Because with this method, I can literally half the time of this search, that sometimes can take up to 10 second!!

enter image description here

It is really necessary to use 2 query, or it is possible in some way to override this logic? Or maybe it's me that I'm missing something behind this logic?

How could you use PHP count if you are paginating? PHP count would return the number of items per page, instead of the count of the whole items. That's why you have to it twice, one to count all the items and another one to fetch the items limited by the amount you specify and by the offset(page) — Fabio Antunes
I did a var_dump(count($results)) and it returns the total count of the collection, not the count per page, the same number that the select count() gives — RDev

absolute absolute · Accepted Answer · 2018-02-14T17:20:48

The query is executed twice, once to get the total number of records returned by the final query, and the second time to return only the required dataset.

So, if you have a table of 100,000 records, the first query will count the records returned by the SQL query, let's say 8,900 records match your requirement, it will return an integer of 8,900.

The second query then uses the page number you want, multiples it by the count per page, and then returns the relevant 15 or so records from just this page, which is the LIMIT and OFFSET values within your SQL query.

It is worth noting that GROUP BY paginated results are not handled in the same way. If you add GROUP BY to the end of any SQL statement within eloquent, it returns a single SQL query. This query grabs all the relevant datasets, counts the number of rows returned, and then slices the array to return just the 15 or so records you require.

The difference between these two methods is the first returns 2 tiny query responses. Firstly the total count, and secondly 15 or so datasets from your table.

The GROUP option returns a dataset for EVERY record which matches your SQL requirements. If this is a total of 8,900 records, it will be a total of 8,900 eloquent model objects.

As you can see, if you have a database with a good number of records in it, the the second method, while it may execute the SQL statement quicker, will tie up a lot more resource.

If your SQL statements are taking too long to execute twice, you may need to consider optimising your table, or adding a further INDEX. Just a thought.

Laravel pagination - Select Count() query

1 Answers