Wordpress database performance

[Atheus]

Hi all - I'm working on a site which is based on a heavily customised Wordpress install. Dozens of 'deals' (wordpress posts) are added every day and right now we're up to several thousand in total. Each of the posts has an expiry date and a location, among other custom fields.

So when you visit the front page, it sorts through the posts by location and then by expiry date, before displaying them ordered by expiry date. Obviously excluding the expired ones and any not in the selected location. This is getting extremely slow.

Can I simply add an index to meta_value in wp_postmeta? This would index loads of stuff unnecessarily. Or should I be storing this data in my own table? How should I go about using my own table in the wordpress loop?

 

[johnny.dacu]

I use WP as CMS from small websites. I've never worked on a large website and I have no staight answer. So.. i can't wait for a PHP guru to respond. Or you have found the answer?

 

[elconejito]

by "heavily customized Wordpress" do you mean the theme/layout? or you've acutally customized Wordpress itself?

I don't think Wordpress is what you would want to use for that. Each "post" has a lot of extraneous details that you probably don't need in each listing but that Wordpress has to deal with.

I think ideally you would want to setup a separate table(s) for these listing. You can either have the CMS for it separate from Wordpress (easiest/quickest) or create some sort of plugin where you can access that table from within the wordpress admin (harder i think).

Another thought, is if the data isn't necessarily always pulled dynamically you could just cache the page to html, only updating when a "deal" is updated. that will significantly reduce the DB/PHP workload on the server. There is at least one wordpress plugin out there that does it, possibly more.