Please Improve The Scalability Of The MySql Database

On thing – I fear I have overstated the “killing…” of my server bit. I aim that we should have a workable search facility for our Viewers, which serves the content effectively.

6-10 secs to render results across the total content – seems way too much to me and hence “killing”!

I get fed up at the inability of WordPress search results to paginate to the correct last viewed page correctly when pressing the back button – never mind waiting 10 seconds for a search result

Queries are made by the author of each theme or plugin, so you’d need to talk to each of those. Of course this does imply that you’re not able to do any of this yourself.

Surely it is the interests of both me and them to have this corroborated and resolved [not least having regard to “we do not support customised Themes….etc” caveats precluding the risk of me getting my hands too dirty]

That’s whats killing your server, and I’ll bet it has almost nothing to do with your theme and/or plugins

Have carried out several PageSpeed [et al] assessments and the Themes is the biggest culprit left – including Javascript blocking code [evidently] as well as un-compressed CSS – which I guess has an impact on “Server access” speeds.

Both former – are linked to Plugins and principally the Theme.

Just implemented GZIP compression/minify/expires caching/Nginx
and increased key_buffer_size – so await a few days to see what improvements show

If you’re having problems inserting that many records at once, break it up into smaller chunks, say 1,000 to 2,000 records each.

Perfectly sensible and followed as a procedure already….but I have largely factored and discounted this area

OK, so from that it looks like the main issue with your sites loading is from the JS and CSS files. The SQL queries would be slowing down the searches, but there would be a reason for that…

I’ll bet that your search isn’t the standard WordPress search, but is a search for job listings… which I would also bet is a custom post type that’s been added in by the theme or a plugin. Job listings have a lot of options to search by, don’t they? That means joins, and lots of them. Do you know what slows down SQL queries? Joins that don’t query columns with indexes.

As I’ve stated before, you cannot index everything. That would mean 1,000’s of indexes and would lead to your database size blowing up by a huge amount. I don’t mean 50-60%, I mean 1,000% and up. That is why you need to know what the sloq queries are. Without knowing what the queries are actually querying you can’t create indexes and you can’t optimise anything. The only thing you can do is throw more and mor ehadware at it, which is also a completely valid option and something that you would have to look at for a serious site anyway.

Surely it is the interests of both me and them to have this corroborated and resolved [not least having regard to “we do not support customised Themes….etc” caveats precluding the risk of me getting my hands too dirty]

Firstly, commercial themes and plugins aren’t supported because we can’t see the source code and we have no idea of how they work. As I’m sure you can appreciate, without knowing how something works there’s no possible way to offer any ideas on how to make it work better. On top of that you’re also paying for support, not just code. So as you’re paying for the support by the people that know the code, it would seem strange to not use that, but come here asking for help from volunteers that don’t know anything about the code that you’re using.

It would be great for you and them to corroborate and get this worked out. What have you talked to the theme/plugin authors about so far, and what were their responses?

And there’s nothing at all stopping you from getting your hands dirty. In fact it’s highly recommended that you do! That’s how we’ve all learnt what we know, and it’s how we all will keep on learning. If you can dig into the code you’ll be able ot find out what queries are beign used, and you’ll be able to see what sort of optimisation is able to be done. Without doing that, you’ll always be at the mercy of someone else telling you what can and can’t be done.