Wordpress Calendar Plugin Optimization
Recently, I had to use the super handy Wordpress Calendar Plugin. After adding a few repeating events and taking a look at the output it generated, I noticed the large number of queries that it executed! It executed at least 3 queries per day of the month being displayed! I was doing development that required displaying two months at a time, and the total number of queries was over 150!!
The plugin was fetching the events per-repetetition (weekly, monthly, yearly, etc) per-day. This would add up to more than 3 queries per day in the month.
Well, I decided to step in and take some action. I spent well over ten hours optimizing the event-retrieval process and came up with a query that fetched the events and returned them in chronological order, allowing for filtering of past or future events. I had never done this type of system before, so I took a look at the author’s code and converted it to a very large mysql query. I’m posting it bellow for anyone who might be interested.
The following function fetches the latest events and sorts them using an additional column “diff“. which indicates the freshness of the event. A negative number indicates a past event. A positive number indicates a future event, and a value of 0 indicates an ongoing event. For infinitely repeating events, the diff will indicate the number of days until the next occurrence; but, for infinitely recurring monthly events, it will return a positive value only if the event ocurred more than 15 days ago.
function grab_latest_events($howmany = 3)
{
global $wpdb;
$events = $wpdb->get_results("SELECT *, if(event_recur = 'Y',
if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats YEAR) >= CURDATE()) AND
(CURDATE() BETWEEN DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR) AND
DATE_ADD(event_end, INTERVAL YEAR(CURDATE())-YEAR(event_end) YEAR)),
0,
if(event_repeats = 0,
DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE())
,if(event_repeats>=(YEAR(CURDATE())-YEAR(event_end)),
DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE()),
DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats YEAR), CURDATE()))
)
)
,if(event_recur IN ('M'),
if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats MONTH) >= CURDATE()) AND # withn range
((MONTH(event_end) = MONTH(event_begin) AND DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin) AND DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end)
) OR (MONTH(event_end) > MONTH(event_begin) AND
(DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end) OR DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin)))
OR (MONTH(event_end) < MONTH(event_begin) AND (MONTH(CURDATE()) >= MONTH(event_end) OR MONTH(CURDATE()) <= MONTH(event_begin))
AND (DAYOFMONTH(CURDATE()) > DAYOFMONTH(event_end) OR
DAYOFMONTH(CURDATE()) < DAYOFMONTH(CURDATE()))
)
),
0, #IF MONTHLY AND WITHIN RANGE, RETURN 0 DENOMINATING AN OCURRING EVENT
if(event_repeats = 0 OR DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE()) > 0,
DATEDIFF(DATE_ADD(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), INTERVAL
MONTH(CURDATE())-MONTH(event_begin)+((DAYOFMONTH(CURDATE())-14>DAYOFMONTH(event_begin))) MONTH), CURDATE())
,
DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE())
)
)
,if(event_recur = 'W' AND
(event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats WEEK) >= CURDATE())
AND if(DAYOFWEEK(event_begin) <= DAYOFWEEK(event_end),
DAYOFWEEK(event_begin) < DAYOFWEEK(CURDATE()) AND DAYOFWEEK(event_end) >= DAYOFWEEK(CURDATE()),
DAYOFWEEK(CURDATE()) >= DAYOFWEEK(event_begin) OR DAYOFWEEK(CURDATE()) <= DAYOFWEEK(event_end))
, 0, #else
if(event_recur = 'W' AND
event_repeats = 0,
abs(DAYOFWEEK(CURDATE())-DAYOFWEEK(event_begin)),
if(event_recur = 'S' AND (CURDATE() <= event_end AND event_begin <= CURDATE()),
0, DATEDIFF(event_begin, CURDATE()))
)
))
) as diff FROM " . WP_CALENDAR_TABLE .
" WHERE (CURDATE() <= event_end AND event_begin <= CURDATE()) OR
(if(event_recur = 'Y',
if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats YEAR) >= CURDATE()) AND
(CURDATE() BETWEEN DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR) AND
DATE_ADD(event_end, INTERVAL YEAR(CURDATE())-YEAR(event_end) YEAR)),
0,
if(event_repeats = 0,
DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE())
,if(event_repeats>=(YEAR(CURDATE())-YEAR(event_end)),
DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE()),
DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats YEAR), CURDATE()))
)
)
,if(event_recur IN ('M'),
if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats MONTH) >= CURDATE()) AND # withn range
((MONTH(event_end) = MONTH(event_begin) AND DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin) AND DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end)
) OR (MONTH(event_end) > MONTH(event_begin) AND
(DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end) OR DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin)))
OR (MONTH(event_end) < MONTH(event_begin) AND (MONTH(CURDATE()) >= MONTH(event_end) OR MONTH(CURDATE()) <= MONTH(event_begin))
AND (DAYOFMONTH(CURDATE()) > DAYOFMONTH(event_end) OR
DAYOFMONTH(CURDATE()) < DAYOFMONTH(CURDATE()))
)
),
0, #IF MONTHLY AND WITHIN RANGE, RETURN 0 DENOMINATING AN OCURRING EVENT
if(event_repeats = 0 OR DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE()) > 0,
DATEDIFF(DATE_ADD(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), INTERVAL
MONTH(CURDATE())-MONTH(event_begin)+((DAYOFMONTH(CURDATE())-14>DAYOFMONTH(event_begin))) MONTH), CURDATE())
,
DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE())
)
)
,if(event_recur = 'W' AND
(event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats WEEK) >= CURDATE())
AND if(DAYOFWEEK(event_begin) <= DAYOFWEEK(event_end),
DAYOFWEEK(event_begin) < DAYOFWEEK(CURDATE()) AND DAYOFWEEK(event_end) >= DAYOFWEEK(CURDATE()),
DAYOFWEEK(CURDATE()) >= DAYOFWEEK(event_begin) OR DAYOFWEEK(CURDATE()) <= DAYOFWEEK(event_end))
, 0, #else
if(event_recur = 'W' AND
event_repeats = 0,
abs(DAYOFWEEK(CURDATE())-DAYOFWEEK(event_begin)),
if(event_recur = 'S' AND (CURDATE() <= event_end AND event_begin <= CURDATE()),
0, DATEDIFF(event_begin, CURDATE()))
)
))
) >= 0)" . //*/
" ORDER BY diff ASC, event_begin=event_end DESC, event_begin ASC LIMIT $howmany");
return $events;
}
The conditional column “diff” cannot be used in the WHERE statement, which forced me to copy the code and paste it inside the where statement for filtering out the past events. Until Next Time!!
Tags: Development, Mysql, Php, Wordpress
