Filtered records count taking a long time

[lainga9]

Summary of problem or feature request

When calculating the total filtered records in a datatable of ~1M record the count is taking up to 40s whereas the query to display the records is only taking 10ms.

Is there any way to disable the filtered records count?

I know there is a method setTotalRecords() which disables the count of the total number of records but would it be possible to add something similar to prevent the count taking place in the filterRecords() method in DataTableAbstract?

System details

• Operating System MacOS Sierra
• PHP Version 7.1.6
• Laravel Version 5.5
• Laravel-Datatables Version ~8.0

[yajra]

Afaik, disabling filtered counts is not yet supported. Will try to dig this option when I got the chance.

On the other hand, have you tried optimizing your queries using indexes and other db optimization tricks?

[lainga9]

Great thanks!

Yeah I've tried to optimise as best I can. We have a table with roughly 50M rows which is well indexed and the select queries are fine, it just seems to be the count that takes ages.

[gmatkowski]

I agree this might be a huge improvement, looking forward to set filtered count manually.

[patrickwarner]

Yes, this is a big problem for me as well.

[mahmutbest]

Yes, I am too need setFilteredCount for big tables.
Could you add feature, please

[patrickwarner]

For anyone interested, I've circumvented this issue temporarily. I have a branch on my fork that adds a function setFilteredRecords() for the query builder datatable. This will allow you to manually set that count. My strategy to deal with large (many millions of rows) Innodb tables is to manually query the count and cache it to a unique key derived from the query that was executed. The query will take a long time to execute the first time around but any subsequent requests are instant.

If you want to include it via a VCS repo in your composer file be sure to make use of an alias so that other package dependencies don't throw versioning errors.

"yajra/laravel-datatables-oracle": "dev-filtered-count as 8.0.x-dev",

[yajra]

@forgottencreature I just saw your changes and I think it's a good implementation for this issue. Would you mind submitting a PR?

BTW, this line is required and a fix added on v8.5.1.

[patrickwarner]

@yajra Sure thing. I'll submit the PR tomorrow.

[patrickwarner]

PR submitted!

[kamrava]

With this PR, Is it possible to completely disable the filtered count?

[patrickwarner]

I considered adding support for that in this PR but I wasn't sure what that would look like given you need a count otherwise it won't know how many pages to display.

[kamrava]

@forgottencreature You're right.
What if count the Total Records and Filtered Records in PHP rather than SQL? Does it have better performance?