The Background/ Setup
I’m working on a web application built through WordPress that keeps track of and makes reports based on testing data. I used Advanced Custom Fields (ACF) to create all of the test fields and it stores them in WP’s default wp_postmeta table. The primary limit here is the table’s key->value pair – ACF “repeater” fields (essentially an array of data) get stored with the key referring to the array name, position, and any sub array keys all as one string (example below).
ACF just killed my application speed when saving and retrieving the data I need, so I wrote some MySQL queries to get the data directly out of the database using WP’s $wpdb->get_results
function.
The Problem
Running a somewhat complex MySQL statement in WP’s $wpdb->get_results
is significantly slower than running the same through phpMyAdmin.
The Query
SELECT ID as part_id, post_title as part_title,
GROUP_CONCAT(m2.meta_value ORDER BY m2.meta_key DESC SEPARATOR ', ') as part_name
FROM `wp_postmeta` m
JOIN wp_posts part ON part.ID = m.meta_value
JOIN wp_postmeta m2 ON m2.post_id = m.meta_value
WHERE part.ID = m.meta_value
AND m2.post_id = m.meta_value
AND m.meta_key LIKE 'participants_%_participant'
AND m.post_id = '51911'
AND post_type = 'participant'
AND post_status = 'publish'
AND (
m2.meta_key = 'first_name'
OR m2.meta_key = 'last_name'
)
GROUP BY ID
ORDER BY post_title
More Details
In phpMyAdmin, the query executes in less than a second. Through get_results, it times out my page.
I’ve eliminated all other code issues by running only this query on a php page (and print_r
on the results, but page times out first).
Error log is verified working and logging, but no errors are recorded from the script.
wp_postmeta has this structure.
As a specific DB snippet example:
meta_id | post_id| meta_key | meta_value
305361 | 5713 | participants_0_participant | 14444
305362 | 5191 | participants_0_participant | 14445
305363 | 5890 | participants_0_participant | 14446
305364 | 14444 | first_name | Joe
/*
`5191` is the class ID that the test is related to
`*_0` refers to the participant position in the participants array
`14444` is the participant unique ID
8/
Classes and Participants are post_types – they all have entries in wp_posts
table.
php.ini includes (verified as set via phpinfo())
memory_limit = 2048M
max_execution_time = 1000
set_time_limit = 1000
I’m aware this database structure is far from ideal for this application (it makes me cry a little on the inside). In the future it will be converted to a more sensible setup, but for now I’m just trying to determine if there’s a reason its unmanageably slow that can be fixed quickly.
If you are really curious, you can take a look!
Depending on what you want to get back from the query, the function will normalize the output and reformat it. This involves looping over results and building new output structures which incur processing penalties.
Since your concern is performance, you can utilize the
query()
method directly and bypass that additional processing:Reference: http://codex.wordpress.org/Class_Reference/wpdb#Running_General_Queries
Just be careful to not introduce potential SQL Injection attacks via this method.
One way to speed up all(?) queries involving wp_postmeta is to take the advice here:
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
It discusses a better schema, especially improving on the indexing.