You need to use a DB backend that supports fulltext indexing for content searchs to be fast. The actual query that you stated runs quickly, is only for the first 50 tickets. I do agree that running the same count() query twice for the same search is sub-optimal. I do not see how you could avoid the count query totally if you are paginating the results.
Cheers, Ken On Tue, Dec 07, 2010 at 05:31:17PM +0000, Justin Hayes wrote: > 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 > # u...@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 > # u...@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 > # u...@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.ha...@openbet.com > >