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

[Kenneth Marshall]

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
> >>>> 
> >>>> 
> >> 
> >> 
> 
>