[deleted]
I've got a monster query that builds the front '30,000 foot view' page of an application. I need it to cross-reference all over the place to keep from duplicating data throughout my database. Here's the query:
select tickets.id,tickets.user_req,tickets.status,tickets.s_desc,tickets.date_entered,tickets.date_target,tickets.priority,tickets.dept,
to_days(curdate())-to_days(from_unixtime(date_entered,'%Y-%m-%d')) as days_since,
sum(actions.hours_est) as est_hours,
sum(actions.hours_act) as act_hours,
a.user_to as user_assigned,
u.dept as user_dept
from tickets,actions,actions a,nuke5.nuke_users u
where tickets.id=actions.tid
and a.id=tickets.mra
and tickets.status!='closed'
and tickets.status!='rejected'
and (a.user_to=u.uname or a.user_to='x')
group by actions.tid
order by date_target asc,priority desc
The biggest problem is the execution time - usually above 15 seconds. Indexing doesn't seem to help. Here's an explain:
+---------+--------+-----------------+---------+---------+-------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+--------+-----------------+---------+---------+-------------+------+---------------------------------+
| actions | ALL | tid | NULL | NULL | NULL | 2159 | Using temporary; Using filesort |
| u | index | dept | dept | 29 | NULL | 421 | Using index |
| tickets | eq_ref | PRIMARY,status | PRIMARY | 4 | actions.tid | 1 | where used |
| a | eq_ref | PRIMARY,user_to | PRIMARY | 4 | tickets.mra | 1 | where used |
+---------+--------+-----------------+---------+---------+-------------+------+---------------------------------+
Any ideas how to optimize this query so that I get a faster response time? As questions as needed. The system is an action-based ticket tracking system that has a static 'tickets' table that is rarely updated and an actions table where the actions are never updated, but are inserted. The tickets table as an 'mra' field that keeps track of the most recent action that changed the assigned person and next due date of the ticket. The nuke5 database is a reference to our PHPNuke intranet database, where the users table is used for login and user data, such as the particular user's department number.
The culprit, I realize, is in the weird 'and (a.user_to=u.uname or a.user_to='x'), because 'or's take a long time. This is where I really need help. I can't get all of the records to return without using an or of some type there.
Any ideas?