1

The Problem

On a site I built, I opted to use custom fields instead of a calendar plugin to organize my posts in a "calendar". In the beginning this seemed like the most logical way to accomplish what I needed. However since importing all the content, things didn't turn out like I expected. My "Day" view takes 2-4 seconds to load ~60-100 items, and my month view takes 15-22 seconds to load ~900 items.

Every post has a event_date field that is stored as YYYYmmdd. Some posts have the option to repeat on a fixed or infinite basis like normal calendar events would. I set up two fields for this, one called repeat with options forever and until. Along with this is a field for on what interval it repeats how_often. The values for that are Daily, Monthly, Weekly, and Yearly. And last but not least some posts have an end_date to specify the end of the event.

Some posts based on another taxonomy are "auto recurring" so to speak and recur on a yearly basis.

The easiest way to get this data and filter it down was to use this meta_query:

<?php
$mq = ['meta_query' =>
        [
        'relation' => 'OR',
            [
                'key' => 'event_date',
                'value' => $date->format('md'),
                'compare' => 'LIKE',
                'type' => 'numeric'
            ],
            [
                 'key' => 'how_often',
                 'value' => array('Daily', 'Monthly', 'Weekly'),
                 'compare' => 'IN'
            ]
        ]
    ]

It does a LIKE for the currently queried date's month and day and another OR for all recurring posts.

Then use a function (omitted) to test each post to see if it was valid for the current day. Then the data is then passed to the view to be shown to the user.

I'm not used to dealing with EAV tables or WordPress in general when it comes to building something custom like this. I'd prefer to have built everything using custom tables but I feared that was going to be harder to implement with WordPress and would limit expansion in the future.

Questions

  1. What else can I do to the query to do as little work as possible to eliminate "extra" posts in PHP that get queried because my query is too broad.
  2. For the month view, I'm only displaying the post categories, the event_date, the title, and the total amount of posts per day. Is it possible to only query for this data to speed up the query?
  3. If all else fails, what's a better way to handle this type of data in WordPress? I'd prefer to stay away from calendar plugins and use "Posts" in some way.

Thanks for reading

1

What else can I do to the query to do as little work as possible to eliminate "extra" posts in PHP that get queried because my query is too broad.

The trouble is, meta queries are slow. Metadata is a simple key => value type database. Keys are indexed, and values are longtext. That's it. No datatypes. No additional indexes. Nothing. And not to mention each "rule" will cost you a JOIN.

They're fine for simple filters/switches/comparisons, but the more complicated you get, the more likely it is you should be using something else.

For the month view, I'm only displaying the post categories, the event_date, the title, and the total amount of posts per day. Is it possible to only query for this data to speed up the query?

If you can change the stored date format to MySQL's default (YYYY-MM-DD), then you could use the casting feature of meta queries for date comparisons:

array(
    'key'     => 'event_date',
    'type'    => 'DATE',
    'compare' => 'BETWEEN',
    'value'   =>  array(
        date( 'Y-m-01', $time = current_time( 'timestamp' ) ),
        date( 'Y-m-t', $time ), // Last day of current month
    ),
),

This might give a boost, but no guarantee.

If all else fails, what's a better way to handle this type of data in WordPress? I'd prefer to stay away from calendar plugins and use "Posts" in some way.

Without a doubt, another table. Primary index would be the post ID, and then the subsequent columns to store your arbitrary event data. I'm not a wiz at db schema, you'd be better off asking for advice specific to your problem on another StackExchange.

Hook onto save_post for updating the table, and use the awesome power of posts_clauses found in WP_Query::get_posts() to implement your joins and custom querying:

function wpse_175152_posts_clauses( $clauses, $wp_query ) {
    $clauses[ 'where' ];
    $clauses[ 'groupby' ];
    $clauses[ 'join' ];
    $clauses[ 'orderby' ];
    $clauses[ 'distinct' ];
    $clauses[ 'fields' ];
    $clauses[ 'limits' ];

    return $clauses;
}

add_filter( 'posts_clauses', 'wpse_175152_posts_clauses', 10, 2 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.