Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Problem searching

I've made a migration from 1.6rc3 to 1.6rc5 and everything worlked fine for a couple of days. Today I found out I was having a problem with searches. I can do them correctly from users who have access to just a few tickets, but when I do them as admin or a user with a lot of tickets I just get a blank page. It happens specially with words as I can search for numbers...

Comments

  • I just made a fresh migration in a test PC I have. I keep having the searching problem besides the fact that the RC3 installation and the RC5 upgrade where satisfactory. Did anybody made a search as admin?
  • It might be worth checking your server logs, to see if the search is timing out?
  • Definitively it's timing out, but it shouldn't. As in version rc3 I didn't have this problem.
    The thing is that i have a department with more than 5000 tickets and when I tried to search on it the site goes down for at least 30 seconds (timeout there).
    The solution I found was deleting a couple of lines of SQL (139 and ahead more or less) in /include/staff/tickets.inc.php. Now I can only make searches on tickets title and subject, but the truth is that's all I need, and the performance is great.
  • We will switch the mass scan to FULLTEXT in the next release. Thank you for the feedback.
  • 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['open']=db_count($openCountSql);
    $stats['answered']=db_count($answeredCountSql);
    $stats['overdue']=db_count($overdueCountSql);
    $stats['assigned']=db_count($assignedCountSql);


    This can be easily adopted for new 1.7.

    Now using the search is not a pain, but a pleasure :)
  • Hello IPX... Thank you for the long writeup. I'm trying to put into place your changes, but am getting an error when doing a search - could you assist?

    I'm replacing
                    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".
    \" OR ticket.subject LIKE '%$queryterm%'\".
    \" OR note.title LIKE '%$queryterm%'\".
    \" OR MATCH(message.message) AGAINST('$queryterm')\".
    \" OR MATCH(response.response) AGAINST('$queryterm')\".
    \" OR MATCH(note.note) AGAINST('$queryterm')\".
    ' ) ';
    }else{
    $qwhere.=\" AND ( ticket.email LIKE '%$queryterm%'\".
    \" OR ticket.name LIKE '%$queryterm%'\".
    \" OR ticket.subject LIKE '%$queryterm%'\".
    \" OR message.message LIKE '%$queryterm%'\".
    \" OR response.response LIKE '%$queryterm%'\".
    \" OR note.note LIKE '%$queryterm%'\".
    \" OR note.title LIKE '%$queryterm%'\".
    ' ) ';


    with

                    $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 commenting out

    // 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 )';
    //}


    But it won't return any results. Any help would be greatly appreciated.
  • Hi there,

    I'm not sure that all your OsTicket code is the same as mine.

    The first thing I'd do is put the debug logging before query execution. So you can verify what is actually executed and test it with any MySql client.

    $query=\"$qselect $qfrom $qwhere $qgroup ORDER BY $order_by $order LIMIT \".$pageNav->getStart().\",\".$pageNav->getLimit();
    error_log('IpDbg: Search query: '.$query);
    $tickets_res = db_query($query);


    In my case the line is written to 'upload\scp\php_errors.log'

    I have also a little bit changed query (maybe it's a little bit adopted for our needs):
    $qselect = '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.* ';
    $qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
    //' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri2 ON ticket.priority_id=pri2.priority_id '.
    //' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() '.
    ' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id';

    $qfromTotal=' FROM '.TICKET_TABLE.' ticket ';



    $qfromTotal is a simplified version for db_count query when OsTicket determines a number of pages of the main query, because for counting purposes we can omit unneeded joins.

    error_log('Dbg: Total: '.$total.' with query: SELECT count(DISTINCT ticket.ticket_id) '.$qfromTotal.' '.$qwhere);
    $total=db_count(\"SELECT count(DISTINCT ticket.ticket_id) $qfromTotal $qwhere\");


    My log lines give (I split SQL into lines for readability):
    Count query:
    Total: 1179 with query: 
    SELECT
    count(DISTINCT ticket.ticket_id)
    FROM ost_ticket ticket
    WHERE 1 AND
    ( ticket.email LIKE '%trouble%' OR
    ticket.name LIKE '%trouble%' OR
    ticket.subject LIKE '%trouble%' OR
    ticket.ticket_id in
    ( select distinct ticket_id
    from ( select ticket_id from ost_ticket_message message
    where message.message LIKE '%trouble%'
    union all
    select ticket_id from ost_ticket_note note
    where note.note LIKE '%trouble%' OR note.title LIKE '%trouble%'
    union all
    select ticket_id from ost_ticket_response response
    where response.response LIKE '%trouble%'
    ) as t
    )
    )


    Search query:

    Search query:

    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 '%trouble%' OR
    ticket.name LIKE '%trouble%' OR
    ticket.subject LIKE '%trouble%' OR
    ticket.ticket_id in
    ( select distinct ticket_id
    from (select ticket_id from ost_ticket_message message
    where message.message LIKE '%trouble%'
    union all
    select ticket_id from ost_ticket_note note
    where note.note LIKE '%trouble%' OR note.title LIKE '%trouble%'
    union all
    select ticket_id from ost_ticket_response response
    where response.response LIKE '%trouble%'
    ) as t
    )
    )

    GROUP BY ticket.ticket_id
    ORDER BY ticket.created DESC
    LIMIT 0,15
Sign In or Register to comment.