Count Query in Laravel taking to long to respond. How to Optimize it ?

Hi, In my database i have like 10-20 companies only but having 7,000 - 8,000 rows in other tables like table1,2,3,4. When i run my query of count it just take too long to respond and my page doesn't even load. Its gives me error

Maximum execution time of 60 seconds exceeded

How can i optimized my query to run faster and dont give me error ?

$company = Companies::select('companies.*','table4.name as type',
                        DB::raw('COUNT(DISTINCT users.id) as total_users'),
                        DB::raw('COUNT(DISTINCT table2.id) as total_val1'),
                        DB::raw('COUNT(DISTINCT table5.id) as total_val2'),
                        DB::raw('COUNT(DISTINCT table3.id) as total_val3'))
                       ->leftJoin('table4','table4.id','companies.type_id')
                       ->leftJoin('users','users.company_id','companies.id')
                       ->leftJoin('table2','table2.company_id','companies.id')
                       ->leftJoin('table5','table5.company_id','companies.id')
                       ->leftJoin('table3','table3.company_id','companies.id')
                       ->groupBy('companies.id')
                       ->paginate($perPage);

bugsysha

4 years ago

Level 61

You should see the query that is executed and then inspect it to see what the bottlenecks are with the EXPLAIN prefix to the query.

Tray2

4 years ago

Level 73

Distinct should usually be avoided since it usually gives a full table scan.

Also why are you joining in tables 2, 3 and 5?

I would most likely do

DB::raw('COUNT(id) FROM table2 as total_val1'),

And so on.

You most likely have a cartesian product in there eating resorces.

princeparaste

4 years ago

Level 1

I am using distinct because if i remove Distinct, it will give me total users persent the table for every company, i want number of users that are part of the company

DB::raw('COUNT(id) FROM table2 as total_val1')

I am getting syntax error on query that you told me.

I also try like this but still giving me error

Companies::selectRaw('companies.*, (COUNT(id) FROM users as total_users) ')->get();

Tray2

4 years ago

Level 73

Try with DB::select instead

DB::select('COUNT(id) totasl_val1 FROM table2'),

princeparaste

4 years ago

Level 1

The DB::select query was not working for me. So i try the "selectRaw"

$company = Companies::selectRaw('companies.*, table1.name as type,
        (SELECT COUNT(id) FROM users WHERE users.company_id = companies.id)  as total_users,
        (SELECT COUNT(id) FROM table2 WHERE table2.company_id = companies.id)  as val1,
        (SELECT COUNT(id) FROM table3 WHERE table3.company_id = companies.id)  as val2,
        (SELECT COUNT(id) FROM table4 WHERE table4.company_id = companies.id)  as val3,
        (SELECT SUM(table4.bill) FROM table4 WHERE table4.company_id = companies.id)  as val4
        ') 
        ->leftJoin('table1','table1.id','companies.type_id')
        ->paginate($perPage);

This works, and is fast, getting no page load error also.

Looks like in previous query i was joining many tables with lots of records thats y it was giving the "Maximum execution time error"

Please or to participate in this conversation.