Why is WordPress’ get_results significantly slower than phpMyAdmin?

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

Read More

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.

Related posts

2 comments

  1. If you are really curious, you can take a look!

    enter image description here

    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:

    $results = $wbdp->query("your query goes here");
    

    Reference: http://codex.wordpress.org/Class_Reference/wpdb#Running_General_Queries

    Just be careful to not introduce potential SQL Injection attacks via this method.

Comments are closed.