[launchbrigade]

The real problem with the query is that it uses GROUP BY on two separate rows. This prevents indexing from working. No index means that as the event type post number grows, performance will become steadily worse. Additionally, because it cannot use the index, it is forced to create a temporary table on disk, containing the number of rows returned by “wp_posts.post_type IN (‘post’, ‘tribe_events’) AND (wp_posts.post_status = ‘publish’)”, which in our case is around 1900 rows – per page load.
— Bennett

[launchbrigade]

We can’t use any of the major caching plugins such as W3 Total Cache, these conflict with existing caches such as varnish and are disallowed by many hosting providers for this reason.

For grins I installed DB Cache Reloaded Fix and this had no effect on query execution time. If you know of a specific caching plugin that addresses this specific problem, I’m happy to give it a try.

Object caches are really a band aid which hides the underlying problem. While I agree that sometimes caches are appropriate, I would argue that this particular issue is a bug. If an SQL query on 1300 posts takes almost 3 seconds to complete on a high performance server, there’s a problem with that query or database schema.

While I appreciate the commitment to make things better with each release, this is an issue that severely impacts several of your users and I think it deserves a high priority.

I took a look at the query and some alternatives and I’ve come up with a few bits of information that might help guide toward a solution.

1. SQL_CALC_FOUND_ROWS is almost always slower – sometimes up to 10x slower – than running two queries.
2. There are many records stored in wp_postmeta for each event. The way meta data is stored in key/value pairs rather than columns is inherently inefficient.
3. Restructuring this query in a way that does not use SQL_CALC_FOUND_ROWS is likely to provide a performance increase on it’s own, but it’s likely going to require a code change to utilize multiple queries and sort in (PHP) memory.
4. Creating a separate database table to store events data is probably the best possible solution, but obviously the upgrade process of moving data from wp_postmeta to a separate table will be problematic for large event calendars with web server timeouts, etc.

Chris

[launchbrigade]

Barry,
we have the dev site hosted on our own high performance platform, we own the hardware, and the servers are not loaded with traffic (i.e. Nginx/PHP-FPM/MySQL Galera/Varnish/etc running on dual quad core Xeon server blades). On that note the site’s home page load time is about double at their hosting provider on a old Plesk based server.

The crux of the issue is the SQL query I provided you which is called by the Event Calendar plugin from the home page. When we execute this query on the SQL server directly the response time is 2.6 seconds (vs. 3.5 page load time from the web server).

Just to be clear this is without a doubt a problem with the Events Calendar plugin. The SQL query is extremely inefficient, and we’ve seen this issue on another site that utilizes the same plugin. It seems that anytime the number of event posts approaches or exceeds 1000, the performance drops off drastically.

Please escalate this to your development team for review. There must be a better way to get the posts out of the database without imposing unreasonable computational burden on the SQL server.

Chris

[Author]

Posts