Speed up count query by removing aggregate functions from SELECT

[Seb33300]

Summary of problem

I have a query looking like this:

$companies = Company::select([
            'companies.*',
            DB::raw('SELECT COUNT(*) FROM orders WHERE orders.company_id = companies.id'),
        ])
        ...

This generates 2 queries:

• first query to count the number of rows (count_row_table)
• 2nd query to select the current page results

This works well, but the count_row_table query is taking a while because we have a lot of companies, and the query includes the aggregation function to count orders, which is useless in the count_row_table query:

SELECT 
    COUNT(*) AS aggregate
FROM
    (SELECT companies.*, (SELECT COUNT(*) FROM orders WHERE orders.company_id = companies.id) FROM companies) count_row_table

If I remove it from the select, the query becomes very fast again:

SELECT 
    COUNT(*) AS aggregate
FROM
    (SELECT companies.* FROM companies) count_row_table

Is there any way to remove it from the count_row_table query?

[yajra]

What version are you using? There was a recent patch https://github.com/yajra/laravel-datatables/pull/3135 similar to this concern.

[Seb33300]

Thanks for your reply. I updated to the latest version and tried again but it did not fix my issue.

However, while reading the comments of the PR, I found the ignoreSelectsInCountQuery() method I can use to ignore the selects in the count query and that's exactly what I was looking for. Using that method fixed my issue.