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