Slow query

[tbar0970]

SELECT service_component.id, service_component.categoryid, service_component.title, service_component.alt_title, service_component.length_mins, service_component.runsheet_title_format, service_component.personnel, service_component.show_in_handout, service_component.handout_title_format, service_component.show_on_slide, service_component.credits, service_component.ccli_number, GROUP_CONCAT(DISTINCT cong.name SEPARATOR ", ") as congregations, IF (LENGTH(service_component.runsheet_title_format) = 0, cat.runsheet_title_format, service_component.runsheet_title_format) as runsheet_title_format , IF (LENGTH(service_component.personnel) = 0, cat.personnel_default, service_component.personnel) as personnel , COUNT(DISTINCT svc12m.id) AS usage_12m, MAX(svc.date) as lastused

FROM service_component 
JOIN service_component_category cat ON cat.id = service_component.categoryid 
LEFT JOIN congregation_service_component csc ON csc.componentid = service_component.id  
LEFT JOIN congregation cong ON cong.id = csc.congregationid  
LEFT JOIN service_item si ON si.componentid = service_component.id  
LEFT JOIN service svc ON svc.id = si.serviceid AND svc.congregationid = cong.id  LEFT JOIN service svc12m ON svc12m.id = svc.id AND svc12m.date > NOW() - INTERVAL 12 MONTH 
WHERE ((service_component.id IN (282, 222, 223, 276, 220)))  OR cong.id IS NOT NULL
GROUP BY service_component.id;



+----+-------------+-------------------+--------+-------------------------------------+-------------------------------------+---------+------------------------------+------+---------------------------------+
| id | select_type | table             | type   | possible_keys                       | key                                 | key_len | ref                          | rows | Extra                           |
+----+-------------+-------------------+--------+-------------------------------------+-------------------------------------+---------+------------------------------+------+---------------------------------+
|  1 | SIMPLE      | cat               | ALL    | PRIMARY                             | NULL                                | NULL    | NULL                         |    4 | Using temporary; Using filesort |
|  1 | SIMPLE      | service_component | ref    | PRIMARY,service_component_cat       | service_component_cat               | 4       | je_cciw.cat.id               |   54 |                                 |
|  1 | SIMPLE      | csc               | ref    | congregation_service_component_comp | congregation_service_component_comp | 4       | je_cciw.service_component.id |    1 |                                 |
|  1 | SIMPLE      | cong              | eq_ref | PRIMARY                             | PRIMARY                             | 4       | je_cciw.csc.congregationid   |    1 | Using where                     |
|  1 | SIMPLE      | si                | ref    | service_item_componentid            | service_item_componentid            | 5       | je_cciw.service_component.id |   28 |                                 |
|  1 | SIMPLE      | svc               | eq_ref | PRIMARY                             | PRIMARY                             | 4       | je_cciw.si.serviceid         |    1 |                                 |
|  1 | SIMPLE      | svc12m            | eq_ref | PRIMARY,datecong                    | PRIMARY                             | 4       | je_cciw.svc.id               |    1 |                                 |
+----+-------------+-------------------+--------+-------------------------------------+-------------------------------------+---------+------------------------------+------+---------------------------------+

[tbar0970]

This query is taking about 10 seconds for a system with lots of services.

[tbar0970]

I think the temp table is inevitable when group-by and distinct is in the mix. But we might need to find a way to avoid joining on to so many services to get the last usage and 12m count.

[tbar0970]

Hitting again. Large temp table sometimes runs into disk space issues. See also #718