[Rt-devel] slow mysql queries

[Matt Wirges]

Howdy,

  We've currently got a relatively small RT (3.4.3) MySQL database
(4.1.9) and we're trying to run some queries that look for a specific
token (like an IP address, for example) in either the ticket subject, or
the ticket content.

The problem we are encountering is that when we perform this search
using the ticket query builder, we get very _very_ slow queries on
quasi-complex searches.  An example search would be to find the word
"hello" in either the Subject or the Content of any tickets.

Building this in the query builder yields the following SQL statement
(Note: I've expanded the query for readability):

 SELECT DISTINCT main.*
   FROM Tickets main , Transactions Transactions_1, Attachments
Attachments_2
  WHERE Transactions_1.ObjectType = 'RT::Ticket'
    AND main.EffectiveId = main.id AND main.Status != 'deleted'
    AND main.Type = 'ticket'
    AND (
          (main.Subject LIKE '%hello%')
          OR (
               (Attachments_2.Content LIKE '%hello%')
               AND (Attachments_2.TransactionId = Transactions_1.id)
               AND (main.id = Transactions_1.ObjectId)
             )
        )
  ORDER BY main.id ASC

This query, on our database of only 570 tickets takes well over an hour
and a half to complete.

However, if we restructure this query, such that the attachment
conditions in the WHERE clause are only performed once (not just each
time the OR clause is evaluated), the query performs in a small fraction
of the time and the results are the same.

 SELECT DISTINCT main.*
   FROM Tickets main , Transactions Transactions_1, Attachments
Attachments_2
  WHERE Transactions_1.ObjectType = 'RT::Ticket'
    AND main.EffectiveId = main.id AND main.Status != 'deleted'
    AND main.Type = 'ticket'
    AND Attachments_2.TransactionId = Transactions_1.id
    AND main.id = Transactions_1.ObjectId

    AND (
          (main.Subject LIKE '%hello%')
          OR (Attachments_2.Content LIKE '%hello%')
        )
  ORDER BY main.id ASC

I've looked around a bit and have seen other complaints about slow
queries, but none in this fashion.  I don't see a way around this
problem though since the only way to search tickets in RT is through
this search interface.

Is this expected behavior?  Are there ways to finagle the query builder
into building this query (and others like it) more efficiently for
MySQL?  At the moment, the only way around this I see is to hardcode
this query into an additional search page.


Thanks,
-matt