Speed up search on 33,000 posts 15M row index

[smittelstaedt09Participant]

I love ASP. Incredibly well built plugin!!!

I have ASP installed on a site I just migrated from Joomla/K2 that went live yesterday.
It has almost 33,000 articles spanning 16 years.

I have stop words enabled but the index table is ~ 14,991,147 rows.
It may get much bigger as I add one more big site to the multi-site network.

The ASP search is understandably sluggish.
I’m curious if I’m exceeding the capabilities of the plugin?

Are there tweaks I should try to speed the search up?
i.e. should I disable the live search so it doesn’t start searching until they hit enter?

Google makes this look way too easy…right?

We’re running on a dedicated server with a decent amount of cores and RAM.

I’ve been having serious CPU overload issues.
I don’t know if any of that is related to ASP, but asking just in case it’s contributing to the load.

[Ernest MarcinkoKeymaster]

Hi,

Thank you for your kind words!

Well, the plugin does not have an upper cap of what it could handle, at this point this might be related to server performance. Indexed queries should still be perfectly fine, your SQL server should be able to handle them.

First I would strongly suggest trying the tips from the fine tuning documentation, especially chapters 1-2.

Aside from that, in your case I have a few more suggestions, which may have actually a great impact on the overall performance:
– If you are using any search filters, I suggest turning them off if possible. Not only visually, actually removing any filters: https://i.imgur.com/PlrkxKq.png
Filters may require table joins or subqueries, which may slow down the query, or SQL may use a non indexed query, which actually can take some time to execute. This can have a really big impact on server usage an performance.
– Using only a primary logic, and not a secondary. In your case, probably the “AND with exact word matches” is a good option, due to the great amount of data to choose from: https://i.imgur.com/ajm9bEL.png
– Running an index table defragmentation will also help: https://i.imgur.com/6VABYng.png

If everything is set up similarly, I think you should expect a decent response time, somewhere around or below 3 seconds if the server is not too busy. But of course this greatly depends on the number of plugins, their code quality – a great disatvantage in worpress is, that in each ajax request the core, all the plugins, themes and everything needs to load up, which add to the response time – usually a lot, sometimes even seconds while everything loads up.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[smittelstaedt09Participant]

Thank you so much for your help.
I’m attacking this from the db server config side too.
I already have primary logic only.
I’ll check/implement your suggestions now.
Have a great day.

[Author]

Posts