Improve OsTicket search speed
Hi there,
We are running OsTicket 1.6 and MySql 6.0.11.We have started experiencing the same problem recently: timeouts on text search.
After changing search queries we've gained a dramatic performance improvement by a factor of 20 (query time got down to 0.7-0.8s from 16-18s), so I'd like to share with you how we did this, and someone might want to update existing OsTicket code.
The problem lies in extensible join usage of tables that aren't used in the result set.
The search code is located in ./include/staff/tickets.inc.php
When a text search is issued, the tickets.inc.php makes 2 queries:
1. Calculates count of the result records to show the pages,
2. Gets the results.
When each query gets more than 15 seconds, 2 ones make PHP page to time out (with default 30 seconds timeout).
When you try to search for a string 'test', the original query looks like (when no additional conditions are specified) :
SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.username ,
ticket.status,ticket.source,isoverdue,isanswered,ticket.created,ticket.lastresponse,pri.* ,count(attach.attach_id) as attachments,
IF(ticket.reopened is NULL,ticket.created,ticket.reopened) as effective_date
FROM ost_ticket ticket
LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id
LEFT JOIN ost_staff staff ON ticket.staff_id=staff.staff_id
LEFT JOIN ost_ticket_message message ON (ticket.ticket_id=message.ticket_id )
LEFT JOIN ost_ticket_response response ON (ticket.ticket_id=response.ticket_id )
LEFT JOIN ost_ticket_note note ON (ticket.ticket_id=note.ticket_id )
LEFT JOIN ost_ticket_priority pri ON ticket.priority_id=pri.priority_id
LEFT JOIN ost_ticket_lock tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()
LEFT JOIN ost_ticket_attachment attach ON ticket.ticket_id=attach.ticket_id
WHERE 1 AND (
ticket.email LIKE '%test%' OR
ticket.name LIKE '%test%' OR
ticket.subject LIKE '%test%' OR
message.message LIKE '%test%' OR
response.response LIKE '%test%' OR
note.note LIKE '%test%' OR
note.title LIKE '%test%' )
GROUP BY ticket.ticket_id
ORDER BY priority_urgency,effective_date DESC ,ticket.created DESC
LIMIT 0,25
Execution time: 15.91s
notice that there joins with messages, notes and response table, that are not selected in the result set, but used for filtering tickets.
It's much faster to look through these tables first to obtain ticket Ids that we are interested in, and then filter only the tickets table, and no need to join with these tables:
SELECT
ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.username ,
ticket.status,ticket.source,isoverdue,isanswered,ticket.created,ticket.lastresponse,pri.* ,count(attach.attach_id) as attachments,
IF(ticket.reopened is NULL,ticket.created,ticket.reopened) as effective_date
FROM ost_ticket ticket
LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id
LEFT JOIN ost_staff staff ON ticket.staff_id=staff.staff_id
LEFT JOIN ost_ticket_priority pri ON ticket.priority_id=pri.priority_id
LEFT JOIN ost_ticket_lock tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()
LEFT JOIN ost_ticket_attachment attach ON ticket.ticket_id=attach.ticket_id
WHERE 1 AND (
ticket.email LIKE '%test%' OR
ticket.name LIKE '%test%' OR
ticket.subject LIKE '%test%' OR
ticket.ticket_id in (
select distinct ticket_id from (
select ticket_id from ost_ticket_message message
where message.message LIKE '%test%'
union all
select ticket_id from ost_ticket_note note
where note.note LIKE '%test%' OR note.title LIKE '%cloudit%'
union all
select ticket_id from ost_ticket_response response
where response.response LIKE '%test%' ) as t ) )
GROUP BY ticket.ticket_id
ORDER BY priority_urgency,effective_date DESC ,ticket.created DESC
LIMIT 0,25
Execution time: 0.748s - 21.1 times faster
Ones might be interested why (select distinct (select... union all select... union all select...)) and not just (select...union select... union select...).
With the latter case there's a big problem when using FULLTEXT indexes (when MATCH AGAIN is used instead of LIKE) - MySql (6.0.11) executes query for almost 4 minutes (!) with 95-100% CPU usage. The "outer" select distinct doesn't lead to such problem and even with LIKEs worjs works 2 times faster than the second method.
In PHP this will result as :
$qwhere.=" AND ( ticket.email LIKE '%$queryterm%'".
" OR ticket.name LIKE '%$queryterm%'".
" OR ticket.subject LIKE '%$queryterm%'".
" OR ticket.ticket_id in ( ".
" select distinct ticket_id from (".
" select ticket_id from ".TICKET_MESSAGE_TABLE." message".
" where message.message LIKE '%$queryterm%'".
" union all ".
" select ticket_id from ".TICKET_NOTE_TABLE." note".
" where note.note LIKE '%$queryterm%' OR note.title LIKE '%$queryterm%'".
" union all ".
" select ticket_id from ".TICKET_RESPONSE_TABLE." response".
" where response.response LIKE '%$queryterm%'".
" ) as t".
" )".
' ) ';
And remove joins with tables in subject:
// These lines are not used anymore !
//if($search && $deep_search) {
// $qfrom.=' LEFT JOIN '.TICKET_MESSAGE_TABLE.' message ON (ticket.ticket_id=message.ticket_id )';
// $qfrom.=' LEFT JOIN '.TICKET_RESPONSE_TABLE.' response ON (ticket.ticket_id=response.ticket_id )';
// $qfrom.=' LEFT JOIN '.TICKET_NOTE_TABLE.' note ON (ticket.ticket_id=note.ticket_id )';
//}
The result count calculation can also be freed from joined tables and grouping aggregates:
SELECT count(DISTINCT ticket.ticket_id)
FROM ost_ticket ticket
WHERE 1 AND (
ticket.email LIKE '%test%' OR
ticket.name LIKE '%test%' OR
ticket.subject LIKE '%test%' OR
ticket.ticket_id in (
select distinct ticket_id from (
select ticket_id from ost_ticket_message message
where message.message LIKE '%test%'
union all
select ticket_id from ost_ticket_note note
where note.note LIKE '%test%' OR note.title LIKE '%cloudit%'
union all
select ticket_id from ost_ticket_response response
where response.response LIKE '%test%' ) as t ) )
The third significant improvement that gave us 1 second more (that's meaningful when you work with web interface) is a stats calculation.
In original code the counts for opened, answered,assigned and overdue tickets are calculated with the single query joining ost_ticket table with itself several times:
SELECT
count(open.ticket_id) as open,
count(answered.ticket_id) as answered ,
count(overdue.ticket_id) as overdue,
count(assigned.ticket_id) as assigned
FROM ost_ticket ticket
LEFT JOIN ost_ticket open ON open.ticket_id=ticket.ticket_id AND open.status='open' AND open.isanswered=0
LEFT JOIN ost_ticket answered ON answered.ticket_id=ticket.ticket_id AND answered.status='open' AND answered.isanswered=1
LEFT JOIN ost_ticket overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status='open' AND overdue.isoverdue=1
LEFT JOIN ost_ticket assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.staff_id=6
Execution time: 1.110s
By simply splitting this select into separate ones:
select count(ticket_id) FROM ost_ticket where status='open' AND isanswered=0;
select count(ticket_id) FROM ost_ticket where status='open' AND isanswered=1;
select count(ticket_id) FROM ost_ticket where status='open' AND isoverdue=1;
select count(ticket_id) FROM ost_ticket where staff_id=6;
Execution time: 0.005s
The PHP snipped for this is quite simple (replace current stats code with the one below in ./scp/tickets.php)
$openCountSql = "select count(ticket_id) FROM ".TICKET_TABLE." ticket where status='open' AND isanswered=0";
$answeredCountSql = "select count(ticket_id) FROM ".TICKET_TABLE." ticket where status='open' AND isanswered=1";
$overdueCountSql = "select count(ticket_id) FROM ".TICKET_TABLE." ticket where status='open' AND isoverdue=1";
$assignedCountSql = "select count(ticket_id) FROM ".TICKET_TABLE." ticket where staff_id=".db_input($thisuser->getId());
if(!$thisuser->isAdmin()){
$nonAdminCond =' AND (ticket.dept_id IN('.implode(',',$thisuser->getDepts()).') OR ticket.staff_id='.db_input($thisuser->getId()).')';
$openCountSql.=$nonAdminCond;
$answeredCountSql.=$nonAdminCond;
$overdueCountSql.=$nonAdminCond;
$assignedCountSql.=$nonAdminCond;
}
$stats= array();
$stats=db_count($openCountSql);
$stats=db_count($answeredCountSql);
$stats=db_count($overdueCountSql);
$stats=db_count($assignedCountSql);
This can be easily adopted for new 1.7.
Now using the search is not a pain, but a pleasure :)