Slow mysql queries

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

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@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@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Best regards, Ruslan.