Unoptimized; DoS risk.

This plugin struggles with scalability issues, particularly noticeable when managing a sizable number of events. Its performance suffers due to inefficient database queries, largely stemming from the lack of proper indexing and the storage of date fields as strings instead of numbers.

Without indexes on date fields, the DATETIME typecasting, and with every date-related query requiring the database to scan through all records, even basic operations can take several seconds. For instance, rendering event views can be sluggish, sometimes taking up to 8 seconds on a site with very many events.

Examining the database structure reveals the absence of indexes for efficient lookups, amplifying the performance problems. Queries like event views further exacerbate the issue by lacking optimization, often necessitating the conversion of VARCHAR strings to DATETIME (integers) types in real-time for each record, because they are used in the query where clause.

This plugin isn’t suitable for sites expecting significant event traffic. Even a modest number of events can strain server resources. To mitigate these issues, the plugin should ideally store date fields as DATETIME types, utilize appropriate indexing, and optimize queries accordingly—a practice notably absent in its current implementation.

The lack of optimization and potential for creating a Denial of Service (DoS) risk is very concerning. The more events you have, the slower each event query will take, consuming a high amount of CPU time in the database server, making CPU time less available for other operations.

You likely won’t notice a load increase in your database server if you don’t have many events. But if you have many events, even with relatively low traffic, if your calendar pages get that traffic, like from search engine bots, your database server will require disproportionally high resources (CPU). If to bots aggressively crawl these URIs, while you have many events, you will likely experience noticable CPU load in your DB server: ^/(all-events|events-all|events|calendar)/

1. Event View Query:

SELECT wp_posts.ID
FROM wp_posts JOIN wp_tec_occurrences
ON wp_posts.ID = wp_tec_occurrences.post_id
WHERE 1=1
AND wp_posts.post_type = 'tribe_events'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'tribe-ea-success'
OR wp_posts.post_status = 'tribe-ea-failed'
OR wp_posts.post_status = 'tribe-ea-schedule'
OR wp_posts.post_status = 'tribe-ea-pending'
OR wp_posts.post_status = 'tribe-ea-draft'
OR wp_posts.post_status = 'private')))
ORDER BY wp_posts.post_date DESC
LIMIT 0, 15

There is no index on wp_tec_occurrences.post_id, so this JOIN is not efficient. To satisify this join, every record in the wp_tec_occurrences table must be read by the databserver to determine selection candidacy.

2. Date-related Query:

SELECT wp_posts.ID, CAST( wp_tec_occurrences.start_date AS DATETIME ) AS event_date, CAST( wp_tec_occurrences.duration AS DECIMAL ) AS event_duration
FROM wp_posts JOIN wp_tec_occurrences
ON wp_posts.ID = wp_tec_occurrences.post_id
WHERE 1=1
AND wp_posts.post_type = 'tribe_events'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
AND (( TIMESTAMPDIFF ( SECOND, wp_tec_occurrences.start_date, '2024-05-06 23:59:59' ) >= 2
AND TIMESTAMPDIFF ( SECOND, '2024-04-01 00:00:00', wp_tec_occurrences.end_date ) >= 2 ))
GROUP BY wp_tec_occurrences.occurrence_id
ORDER BY wp_tec_occurrences.start_date ASC, wp_tec_occurrences.duration ASC, wp_posts.menu_order ASC

There is no index on wp_tec_occurrences.post_id, so this JOIN is not efficient. Additionally, to satisify the criteria, every record in the wp_tec_occurrences table must be read because of the type casting of wp_tec_occurrences.start_date, which is a VARCHAR (string), to a DATETIME, which is an integer. This latter operation also requires CPU cycles. The complexity of this query scales up with the number of events.

3. Another Query:
   SELECT wp_posts.ID, CAST( wp_tec_occurrences.start_date AS DATETIME ) AS event_date
FROM wp_posts JOIN wp_tec_occurrences
ON wp_posts.ID = wp_tec_occurrences.post_id
WHERE 1=1
AND wp_posts.post_password != ''
AND wp_posts.post_type = 'tribe_events'
AND ((wp_posts.post_status <> 'trash'
AND wp_posts.post_status <> 'auto-draft'))
GROUP BY wp_tec_occurrences.occurrence_id
ORDER BY wp_tec_occurrences.start_date ASC, wp_posts.post_date ASC
LIMIT 0, 15

4. Another:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID, CAST( wp_tec_occurrences.start_date AS DATETIME ) AS event_date
FROM wp_posts JOIN wp_tec_occurrences
ON wp_posts.ID = wp_tec_occurrences.post_id
WHERE 1=1
AND ( CAST(wp_tec_occurrences.start_date_utc AS DATETIME) > '2024-04-25 00:00:00' )
AND wp_posts.post_type = 'tribe_events'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
GROUP BY wp_tec_occurrences.occurrence_id
ORDER BY wp_tec_occurrences.start_date ASC, wp_posts.post_date ASC
LIMIT 0, 15

Structures, as the plugin creates them:

CREATE TABLE wp_tec_events (
event_id bigint(20) UNSIGNED NOT NULL,
post_id bigint(20) UNSIGNED NOT NULL,
start_date varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
end_date varchar(19) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
timezone varchar(30) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'UTC',
start_date_utc varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
end_date_utc varchar(19) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
duration mediumint(30) DEFAULT '7200',
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
hash varchar(40) COLLATE utf8mb4_unicode_520_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

ALTER TABLE wp_tec_events
ADD PRIMARY KEY (event_id),
ADD UNIQUE KEY post_id (post_id);

CREATE TABLE wp_tec_occurrences (
occurrence_id bigint(20) UNSIGNED NOT NULL,
event_id bigint(20) UNSIGNED NOT NULL,
post_id bigint(20) UNSIGNED NOT NULL,
start_date varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
start_date_utc varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
end_date varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
end_date_utc varchar(19) COLLATE utf8mb4_unicode_520_ci NOT NULL,
duration mediumint(30) DEFAULT '7200',
hash varchar(40) COLLATE utf8mb4_unicode_520_ci NOT NULL,
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

ALTER TABLE wp_tec_occurrences
ADD PRIMARY KEY (occurrence_id),
ADD UNIQUE KEY hash (hash),
ADD KEY event_id (event_id);