CODEWITHSUNDEEP
mysqlsqlruby-on-rails
Codeninja
Codeninja

Reputation: 103

Optimizing MySQL Count query with multiple joins

I have a SQL Query that's taking over 3.5 seconds to return a count. Looking at the trace of the query it looks like all the indexes are being used. What can I do to optimize this to reasonable execution?

My thanks in advance.

Link to trace

SQL details

Query 

SELECT count(DISTINCT `workorders`.id) AS count_all FROM `workorders` 
LEFT OUTER JOIN `users` ON `users`.id = `workorders`.user_id 
LEFT OUTER JOIN `assets` ON `assets`.id = `workorders`.asset_id 
LEFT OUTER JOIN `users` completed_by_users_workorders ON `completed_by_users_workorders`.id = `workorders`.completed_by_user_id 
LEFT OUTER JOIN `messages` ON messages.workorder_id = workorders.id LEFT OUTER JOIN `priorities` ON `priorities`.id = `workorders`.priority_id 
LEFT OUTER JOIN `suspension_reasons` ON `suspension_reasons`.id = `workorders`.suspension_reason_id 
LEFT OUTER JOIN `job_types` ON `job_types`.id = `workorders`.job_type_id 
LEFT OUTER JOIN `workorder_statuses` ON `workorder_statuses`.id = `workorders`.workorder_status_id 
LEFT OUTER JOIN `workorder_types` ON `workorder_types`.id = `workorders`.workorder_type_id 
LEFT OUTER JOIN `workorder_tasks` ON workorder_tasks.workorder_id = workorders.id 
LEFT OUTER JOIN `frequencies` ON `frequencies`.id = `workorder_tasks`.frequency_id 
LEFT OUTER JOIN `facilities` ON `facilities`.id = `workorders`.facility_id 
LEFT OUTER JOIN `locations` ON `locations`.id = `workorders`.location_id 
WHERE ((((workorders.workorder_status_id = ?)) AND (workorders.application = ?)) AND (workorders.facility_id = ?))

Duration 3,791 ms Query analysis (show details)

Trace *workorders*
The table was retrieved with this index: facility_id,app_fac,workorder_status_id You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.

Approximately 33850 rows of this table were scanned.

users
The table was retrieved with this index: PRIMARY Approximately 1 row of this table was scanned.

assets
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

completed_by_users_workorders
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

messages
The table was retrieved with this index: workorder_id

Approximately 2 rows of this table were scanned.

priorities
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

suspension_reasons
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

job_types
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

workorder_statuses
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

workorder_types The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

workorder_tasks The table was retrieved with this index: index_workorder_tasks_on_workorder_id You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.

Approximately 1 row of this table was scanned.

frequencies The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

facilities
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

locations
The table was retrieved with this index: PRIMARY

Approximately 1 row of this table was scanned.

Upvotes: 0

Views: 249

Answers (1)

a1ex07
a1ex07

Reputation: 37354

Unless I'm missing something, you have bunch of LEFT JOINs which don't affect final results ( you are selecting distinct workorders.id - I guess it's a PK column. Then only where is important , WHERE ((((workorders.workorder_status_id = ?)) AND (workorders.application = ?)) AND (workorders.facility_id = ?) . So I believe you can get rid of all LEFT JOINs and write just select from 1 table making life of optimizer much easier ....

SELECT count(*) AS count_all
FROM `workorders`
WHERE ((((workorders.workorder_status_id = ?)) AND (workorders.application = ?)) AND   
(workorders.facility_id = ?)

The query above should give you the same result as your original query but with much less efforts.

Upvotes: 2

Related Questions