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

[Kenneth Marshall]

On Fri, Dec 10, 2010 at 08:26:58AM +0000, Justin Hayes wrote:
> Any view on which is faster Jesse (postgres or mysql/sphinx)?
> 
> Also how much faster than the old Content search are we talking? Orders of magnitude, or just 'faster'?
> 
> Thanks again,
> 
> Justin
> 

Hi Justin,

I have not benchmarked the MySQL/Sphinx relative to the PostgreSQL
implementation but the few I have found using a search showed their
performance to be pretty comparable. I have not had a chance to look
at how RT integrates with Sphinx, but when I wanted to test it with
a PostgreSQL database, it did not keep its indexes up realtime, but
required a periodic job to run for updates and periodic reindexes.
The PostgreSQL fulltext indexes are kept in sync at all times. This
helps avoid the "the ticket is there but the search did not find it"
syndrome. As far as the speed with the fulltext indexing versus no
fulltext indexing, a sample search that I ran while testing took
20 minutes for the table scan versus a couple of seconds for the
search with the index support, 600X faster. Of course, the bigger
win is that your I/O system is not tapped out while you are
searching in the content and you database scales much, much more
gracefully.

Cheers,
Ken

> -------------------------------------------------
> Justin Hayes
> OpenBet Support Manager
> justin.hayes at openbet.com
> 
> On 8 Dec 2010, at 16:48, Jesse Vincent wrote:
> 
> > 
> > 
> > 
> > On Wed  8.Dec'10 at 16:44:57 +0000, Justin Hayes wrote:
> >> I was about to say that should have been 3.9 :)
> >> 
> >> Been a while since I checked out bestpractical.com. Just saw the post about the dev release, and looks like things are going well which is great news.
> >> 
> >> The only downside is the time needed to migrate to postgres, 
> > 
> > Or you could try the sphinx-based fts for mysql that's baked in
> > 
> >> and then the time it's going to take to port all my custom code into the new version.
> >> 
> >> But that's my problem, and one I have every time I upgrade, so can't complain!
> > 
> 
>