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 );