Slow MySQL Query Breaking my back!
- by Chris n
so, I have tried everything I can think of, and can't get this query to happen in less than 3 seconds on my local server. I know the problem has to do with the OR referencing both the owner_id and the person_id. if I run one or the other it happens instantly, but together with an or I can't seem to make it work - I looked into rewriting the code, but the way the app was designed it won't be easy. is there a way I can call an equivalent or that won't take so long? here is the sql:
SELECT event_types.name as event_type_name,event_types.id as id, count(events.id) as
count,sum(events.estimated_duration) as time_sum FROM events,event_types
WHERE event_types.id = events.event_type_id AND events.event_type_id != '4'
AND ( events.status!='cancelled')
AND events.event_type_id != 64
AND ( events.owner_id = 161 OR events.person_id = 161 )
GROUP BY event_types.name
ORDER BY event_types.name DESC;
Here's the Explain soup, although I'm guessing it's unnecessary cause there is probably a better way to structure that or that is obvious:
thanks so much! chris.
+----+-------------+-------------+-------+---------------------------------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------------------------------------------------------------------------------------------------+-------------------------------+--
| 1 | SIMPLE | event_types | range | PRIMARY | PRIMARY | 4 | NULL | 78 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | events | ref | index_events_on_status,index_events_on_event_type_id,index_events_on_person_id,index_events_on_owner_id | index_events_on_event_type_id | 5 | thenumber_production.event_types.id | 907 | Using where |
+----+-------------+-------------+-------+---------------------------------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------+------+----------------------------------------------+
