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

[Justin Hayes]

Thanks Ken. I certainly prefer the idea of using inbuilt DB server functionality to improve performance than an external tool hooked in.

I'll probably look into a PostgreSQL migration first and see where that takes me. I hope it's *just* a case of creating the schema by running a 3.8.8 install against it, dumping the data and loading it in ;)

Justin

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

On 10 Dec 2010, at 14:46, Kenneth Marshall wrote:

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