Laravel-datatables: Speeding up or disabling entries count

[marijnhurkens]

I really like this package and I am using it in several projects, but I have a problem with the speed of the queries it performs.

My situation is as follows:

I am retrieving my information from a table called Students while also joining 2 tables called Users and Addresses. I also have some extra filters next to the global search. Using the filter option of this package disables the global search, so I am filtering manually before sending the query to Datatables::of($query).

This all works great, but the entries count query is taking a 150 ms to complete, while the main select query is taking 6 ms. The database has about a 1000 entries and is expected to grow to 10,000+.

My code:

$students = Student::join('users', function ($join) {
            $join->on('users.owner_id', '=', 'students.id');
            $join->on('users.owner_type', '=', 'App\Models\Student');
        })
            ->join('addresses', function ($join) {
                $join->on('addresses.owner_type', '=', 'App\Models\Student');
                $join->on('addresses.owner_id', '=', 'students.id');
            });

        if ($request->has('city')) {
            $students->where('addresses.city',  $request->get('city'));
        }

        if ($request->has('status')) {
            $students->where('students.status',  $request->get('status'));
        }

        $students->select('students.created_at', 'students.first_name', 'students.last_name', 'users.email',
            'students.telephone_number', 'students.gender', 'students.status', 'students.id', 'addresses.city');

        return Datatables::of($students)
            ->editColumn('created_at', '{!! $created_at->toDateString() !!}')
            ->make(TRUE);

The resulting query for the entries count is (157.07 ms):

select count(*) as aggregate from (select '1' as "row_count" from "students" 
inner join "users" on "users"."owner_id" = "students"."id" and "users"."owner_type" = "App\Models\Student" 
inner join "addresses" on "addresses"."owner_type" = "App\Models\Student" and "addresses"."owner_id" = "students"."id" 
where "students"."deleted_at" is null) count_row_table

And the main select query (5.98 ms):

select "students"."created_at", "students"."first_name", "students"."last_name", "users"."email", "students"."telephone_number", "students"."gender", "students"."status", "students"."id", "addresses"."city" 
from "students" inner join "users" on "users"."owner_id" = "students"."id" and "users"."owner_type" = "App\Models\Student" 
inner join "addresses" on "addresses"."owner_type" = "App\Models\Student" and "addresses"."owner_id" = "students"."id" 
where "students"."deleted_at" is null order by "students"."created_at" desc limit 10 offset 0

Is there any way to disable the entries query, cache it or otherwise improve the speed?

Thanks in advance!

System details:

Windows 10
PHP 7.0.5
SQlite
Laravel 5.2
Laravel Datatables 6.11.3

[yajra]

@marijnhur, there is currently no way to customize the count query but I think you can speed up the query by adding the proper indexes for each tables. For instance, does you owner_id & owner_type column on address table has the proper index?

On the other hand, I do have projects that contains thousands to million of records and pagination/count works well for me.

[robertbakker]

I would very much like to customize it too.

I have the same problem where my main query is 8 ms, and the count query is taking over 5s on a database table with about 100,000 records.

select count(*) as aggregate from (select '1' as `row_count` from `properties` where `properties`.`deleted_at` is null) count_row_table

5.36s

When I make a simplerer count query, so without the subquery, it takes about 2 ms. So the problem really lies in this query for my situation.

Is there maybe a way to replace the EloquentEngine ?

Cheers for the good work though!

[yajra]

@robertbakker, I just encountered the same performance issue on count when building a complex SQL like for my case, having a UNION ALL with multiple JOINS.

In this regard, I added a new method setTotalRecords($total) to manually set the total records and skip the counting query.

$builder = app(MyQueryBuilder::class);

return $this->datatables
            ->queryBuilder($this->query())
            ->setTotalRecords($builder->count())
            ->make(true);

[yajra]

Please checkout v6.13.1 and verify and close this issue if fixed. Thanks!

[robertbakker]

@yajra Simple yet effective, thanks a lot, exactly what I needed!

Not sure if I can close this issue however.

[marijnhurkens]

@yajra Thanks a lot!

[rafaelqm]

@yajra after any filter the counting query executes again, letting the query slow. There are any workaround to this?

[yajra]

@rafaelqm you can use setFilteredRecords to disable counting of filtered.

[karmendra]

I am wondering if we can add feature to cache the count query instead of skipping it, specially if the number of records are above a threshold like 10K.