High Load Killing MYSQL

when i run the above query manually I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

I recently upgraded my database server from 5.1 to 5.5 mysql does that have anything to do with it

ok query works it just cut off some end brackets in log, however mysql is getting hammered what is solution

I think I find the answer in that developer does not recommend use on high traffic large database sites…. yes because you wrote it in bad way that absolutely cains db access. I have 250000 posts and this is a fail.. ok I will delete this is v bad

Yes, that’s about ten times the maximum size I recommend for Relevanssi. Relevanssi works up to tens of thousands of posts, in most cases. So, you’re way out of the league; any local solution is probably bad for you. I’d very much look for external solutions.

Actually I have stopped much of the mysql issues if I set a limit for searches of about 100 and add a load of mysql indexes on the relevanssi tables in wordpress. You should have indexes for those tables by default to make it faster to find the right data, I have tens of millions of records in wp_relevanssi for example. I guess combined with object caching and query caching etc it is ok now. It still runs a little high when there are many searches at once but is useable at the moment without breaking mysql. I used this plugin to begin with because I wanted something to custom weight pages based on type and divide my search results into categories so a few categories would show first, which actually works well and exactly how I want it to with my custom functions in functions.php. I mean as it is now it would actually work ok but searches take a few seconds to load page so will probably migrate over to Apache Solr instead which I already have installed on my server but never got around to looking into it and working it out properly

People are already complaining how big the Relevanssi database is, adding more indices would make it even bigger. The most important indices are already there.

I very much recommend you don’t use Relevanssi for something that big, it’s just not going to work in the long run. Solr is probably a much better idea, or something like Swiftype or AddSearch.