[rt-users] Content searching takes a long time and runs multiple queries

[Justin Hayes]

Guys,

Searching for ticket content takes forever. I've done a bit of digging and for a single search in one of my queues over the last year, RT spawned 3 separate queries.

2 are counts (which appear to be identical), and 1 gets the actual content.

Is there anyway round this? Losing loads of time just to get counts seems rather counter-productive? The final select was actually pretty quick.

I've added the queries below.

Many thanks,

Justin

# Time: 101207 17:24:09
# User at Host: rt_support[rt_support] @ localhost []
# Query_time: 57.722237  Lock_time: 0.000183 Rows_sent: 1  Rows_examined: 122794
SET timestamp=1291742649;
SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id )  WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND (main.Created > '2010-01-01 00:00:00' AND main.Queue = '4' AND Attachments_2.Content LIKE '%testing%') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id);

# Time: 101207 17:24:38
# User at Host: rt_support[rt_support] @ localhost []
# Query_time: 28.780620  Lock_time: 0.000510 Rows_sent: 1  Rows_examined: 122794
SET timestamp=1291742678;
SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id )  WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND (main.Created > '2010-01-01 00:00:00' AND main.Queue = '4' AND Attachments_2.Content LIKE '%testing%') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id);

# Time: 101207 17:24:42
# User at Host: rt_support[rt_support] @ localhost []
# Query_time: 4.492875  Lock_time: 0.000175 Rows_sent: 50  Rows_examined: 100799
SET timestamp=1291742682;
SELECT DISTINCT main.* FROM Tickets main JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id )  WHERE (Transactions_1.ObjectType = 'RT::Ticket') AND (main.Status != 'deleted') AND (main.Created > '2010-01-01 00:00:00' AND main.Queue = '4' AND Attachments_2.Content LIKE '%testing%') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY main.id ASC  LIMIT 50;

-------------------------------------------------
Justin Hayes
OpenBet Support Manager
justin.hayes at openbet.com