Thread: Advanced Search Performance Improvments

Advanced Search Performance Improvments

I've got a site with over 17000 ads on it and advanced (refined) search has been very slow - over 30 seconds for the majority of searches. Perusing the forums, it seems a number of others have the same problem.

I have the W3 Total cache plugin installed using the opcode page cache, which improved general site performance a great deal (thanks to the forums for the recommendation), but not advanced search - which I would expect, as the majority of the advanced searches would not be pre-cached.

I've had a look through the database architecture and how it's used and have come to what I think is a pretty inevitable conclusion. Classipress stores most ad fields within the wp_postmeta table, where each row represents a single field. While this might make sense from the point of view of making it easier to code for custom fields, from a performance perspective it sucks no end when you try to query it.

I have extended the Classipress plugin and created tables for my ads that are appropriately designed and indexed for the data I have. The immediate effect was to take advanced search queries from over 30 seconds, to under 3. That's over an order of magnitude improvement directly resulting from designing the database appropriately for the data I have.

If Classipress is ever going to be useful out-of-the-box for sites with a reasonably large number of ads, some serious work is going to have to be done in the way the database is architected. This will mean more code to manipulate the structure of the database to deal with custom fields, but I think it's inevitable if performance is ever going to be reasonable.

One immediate recommendation I have - even without changing the database design - is to move the location search code from the pre-posts filter to the join filter. It'll cut down on a query and should make location-based searches much faster.