Skip to content

Speed up count query by removing aggregate functions from SELECT #3159

Closed
@Seb33300

Description

@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?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions