It looks like you're new here. If you want to get involved, click one of these buttons!
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:
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?