Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Time execution comparisons

mcu_hqmcu_hq yippie ki-yay ✭✭✭
edited February 2013 in Vanilla 2.0 - 2.8

So I finally managed to upgrade my POS server laptop to something that was at least constructed in the 21st century. @nfalcone gave me a dump of his database after having timeouts with the search. The DB consumed around 15 Gigs after being rolled out with a little over 3 Million rows in the comments table.

Anyways, I found a pretty shameful bug in all of the sphinx releases that actually caused the sphinx search to run alongside the MYSQL one, hence negating any performance benefits. Of course, this really isn't apparent till you are playing around with huge databases. And so, here is a simple time comparison of a DB this size using both sphinx and the generic MYSQL search that ships with Vanilla. The following are just 2 snapshots of a much greater stack trace:

mysql search

Notice how the above pic shows 183k ms, or 183 seconds! It also created a temporary sorting file of 200MB.

Here is a second with the bug fix where it is only sphinx searching for the same query:

This time the search takes nearly 3k ms , or 3 seconds. Sphinx actually only takes a fraction of that time while the rest is setup plus overhead all on my shit hardware with a huge DB.

Is there any way to improve the default search for large databases? I've seen other forum packages constructing hashes of words to search against using MYSQL, which is sort of what sphinx does internally. Even on this main discussion site, it is troublesome to search for anything. Perhaps only the discussion titles should be searched against?


  • Options
    fr3em1ndfr3em1nd ✭✭
    edited February 2013

    @mcu_hq , i struggle same issue coz i have huge database and community. I'm not sure what solution you like for your problem but for me, as for searches i used vanoogle to let google search through my forums. what i did is i made sure google has indexed all my forum contents and add vanoogle plugin.

    in terms of size of the search and database processor consumption, make sure PDO is enabled in your PHP configuration and make use of memcached

  • Options

    @fr3333333x he is talking about sphinx search which is a search engine. Google site search is fine, but you can't use it on a private forum, and it is not perfect.

    grep is your friend.

  • Options

    yeah ok i was talking about some alternatives if not Sphinxsearch

Sign In or Register to comment.