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 [email protected] 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 >> [email protected] >> >> 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 >>>> [email protected] >>>> >>>> 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 >>>>>> # 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 >>>>>> [email protected] >>>>>> >>>>>> >>>> >>>> >> >>
