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

[Justin Hayes]

I hadn't actually realised it was possible to get hold of 4.9, though if that's beta I guess I'd want to wait.

Postgres might be the way to go then. Will have to look into how difficult a migration would be.

http://requesttracker.wikia.com/wiki/PostgreSQLFullText

Is that the relevant wiki page for getting the searching to run fast Ken?

Thanks,

Justin

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

On 8 Dec 2010, at 13:54, Kenneth Marshall wrote:

> Given that you are familiar with PostgreSQL already, I would use
> it because the current versions of RT support the fulltext indexing
> already and you have fewer moving pieces. If you are already running
> the 4.9.x series, then you could certainly test the sphinx integration.
> 
> Cheers,
> Ken
> 
> On Wed, Dec 08, 2010 at 09:55:06AM +0000, Justin Hayes wrote:
>> Hmm we were looking at sphinx. Would you suggest plugging that in rather than migrating to postgres (which we're also familiar with)?
>> 
>> Justin
>> 
>> -------------------------------------------------
>> Justin Hayes
>> OpenBet Support Manager
>> justin.hayes at openbet.com
>> 
>> On 7 Dec 2010, at 19:40, Kenneth Marshall wrote:
>> 
>>> Hi Justin,
>>> 
>>> In the wiki, there are fulltext index modifications for Oracle and
>>> PostgreSQL. I based the PostgreSQL version on the Oracle version
>>> and we use it here. It works very well indeed. It looks like the
>>> pre version rt-4, a.k.a rt-3.9.6 support Oracle and PostgreSQL
>>> using their fulltext support and MySQL using sphinx, pretty cool.
>>> 
>>> Regards,
>>> Ken
>>> 
>>> On Tue, Dec 07, 2010 at 05:49:12PM +0000, Justin Hayes wrote:
>>>> 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
>>>>>> 
>>>>>> 
>>>> 
>>>> 
>> 
>>