[Rt-devel] slow mysql queries

[Ruslan Zakirov]

You could have tickets that have no attachments at all and query
you're suggesting skip such tickets even if subject contains word you
need.

Searches by attachments content is bad idea because of no indexes on
this field, in new MySQL versions 4.1 and higher you can use FULLTEXT
index, but I didn't investigate if it possible or not to use it with
RT DB.
 

On 8/24/05, Matt Wirges <wirges at purdue.edu> wrote:
> 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
> _______________________________________________
> Rt-devel mailing list
> Rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> 


-- 
Best regards, Ruslan.