[rt-users] Content searching takes a long time and runs multiple queries
Justin Hayes
justin.hayes at openbet.com
Tue Dec 7 12:49:12 EST 2010
Hi Ken,
I was just thinking the same about the counts - it has to do that for pagination. Though I guess it could have been written to run 1 query for all the data, and just display the first 50 etc.
Which DB backend would work faster?
Thanks,
Justin
-------------------------------------------------
Justin Hayes
OpenBet Support Manager
justin.hayes at openbet.com
On 7 Dec 2010, at 17:40, Kenneth Marshall wrote:
> 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
>> # 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
>>
>>
More information about the rt-users
mailing list