$wpdb->query() vs. $wpdb->get_results() vs. phpMyAdmin

admin管理员组

文章数量:1122832

I have this code:

$query = "SELECT * 
            FROM $wpdb->posts
                INNER JOIN $wpdb->postmeta 
                    ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                INNER JOIN $wpdb->term_relationships 
                    ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
            WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));";

$results = $wpdb->query($query); // Takes 1.5 seconds

I also tried this:

$query = "SELECT * 
            FROM $wpdb->posts
                INNER JOIN $wpdb->postmeta 
                    ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                INNER JOIN $wpdb->term_relationships 
                    ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
            WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));";

$results = $wpdb->get_results($query); // Still takes 1.5 seconds

But when I grab the query and put it in phpmyadmin:

(18588 total, Query took 0.0102 seconds.)
SELECT * FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes')) AND (wp_dev_posts.ID = wp_dev_postmeta.post_id) AND (wp_dev_posts.ID = wp_dev_term_relationships.object_id)
// Takes 0.0102 seconds

Why do both $wpdb methods take so much longer? The same query is copy and pasted directly on phpmyadmin, it takes the expected amount of time.

I have this code:

$query = "SELECT * 
            FROM $wpdb->posts
                INNER JOIN $wpdb->postmeta 
                    ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                INNER JOIN $wpdb->term_relationships 
                    ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
            WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));";

$results = $wpdb->query($query); // Takes 1.5 seconds

I also tried this:

$query = "SELECT * 
            FROM $wpdb->posts
                INNER JOIN $wpdb->postmeta 
                    ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                INNER JOIN $wpdb->term_relationships 
                    ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
            WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));";

$results = $wpdb->get_results($query); // Still takes 1.5 seconds

But when I grab the query and put it in phpmyadmin:

(18588 total, Query took 0.0102 seconds.)
SELECT * FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes')) AND (wp_dev_posts.ID = wp_dev_postmeta.post_id) AND (wp_dev_posts.ID = wp_dev_term_relationships.object_id)
// Takes 0.0102 seconds

Why do both $wpdb methods take so much longer? The same query is copy and pasted directly on phpmyadmin, it takes the expected amount of time.

• query
• wpdb
• phpmyadmin

Share Improve this question edited Aug 2, 2019 at 19:02 Justin J James asked Aug 2, 2019 at 18:38 Justin J JamesJustin J James 3911 silver badge77 bronze badges 5

• I answered based on the difference between the WPDB methods as I originally thought that's what you were asking. But it appears you are actually asking why it takes longer to run it through the WPDB object rather than as a query in phpMyAdmin. If that's what your question is, I'll change my answer, but you should also edit your question to clarify exactly what it is you're asking. – butlerblog Commented Aug 2, 2019 at 18:49
• PHPMyAdmin will typically automatically limit the number of results returned to 25 by default. Are you that’s not what’s happening? – Jacob Peattie Commented Aug 3, 2019 at 1:18
• How did you measure the query time? Because I used the exact query (except different post types) and the query time was ~0.002 seconds both using wpdb::query() and phpMyAdmin. And the query time, I checked it via var_dump( $wpdb->queries ); (just find the relevant query) - the query time is the second item in each array in the $wpdb->queries which is an array of queries. See SAVEQUERIES for details on enabling the $wpdb->queries. – Sally CJ Commented Aug 3, 2019 at 1:40
• @SallyCJ I'm measuring it with a call to: $time = (microtime(true) - $_SERVER['REQUEST_TIME_FLOAT']); – Justin J James Commented Aug 6, 2019 at 16:31
• 4 You're measuring the script execution time, but what you see in phpMyAdmin is just the time that was taken to execute the SQL query. Try installing Query Monitor, run your query, and check the Query Monitor (QM) menu in the admin bar. Then just find your query and check the query time - example. – Sally CJ Commented Aug 6, 2019 at 23:23

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

The difference is what is returned.

The WPDB query() method returns true for CREATE, ALTER, TRUNCATE and DROP queries, an integer of how many results for all other queries, or simply false if if there's an error.

The WPDB get_results() method actually returns the entire result of the query (like you would get running the query in phpMyAdmin). You can return the result as an array or an object, depending on how you want to work with the results.

Without much luck, I solved my own problems by using get_transient(...) and set_transient(...)

Note, this is a wordpress-only fix. The first call has someone suffer, but once cached it's a good enough solution for me.

In my case, it was acceptable since I am fine with storing the result for the entire day. Even 12 hours is fine for me. I'm still looking for answers to the original question since I'm very curious what is going wrong.

本文标签： wpdbgtquery() vs wpdbgtgetresults() vs phpMyAdmin