Query Select … execution time

  • guardiano78

    (@guardiano78)


    2 years ago

    Hello,
    more than anything it is a curiosity.
    I created a function to retrieve a user’s id and email, compared only the woocommerce meta_value field which is called “billing_company”.

    Initially I used the method with arguments:

    $args['meta_query']['relation'] = 'AND';
        $args['orderby'] = 'length(meta_value)';
        $args['order'] = 'ASC';
        $args['limit'] = '1';
        foreach($nominativo as $tag) {
            if (trim($tag) != '')
            {
                $args['meta_query'][] = array( 'key' => 'billing_company', 'value' => trim($tag), 'compare' => 'LIKE' );    
            }
        }
$user = get_users($args);

    It works, but it’s not very accurate because it doesn’t seem to fully accept the length(meta_value) function in order by.
    So I was forced to use $wpdb doing a classic select with the same features. Now it works perfectly, except for cases of homonymy, but there is nothing we can do about it.

    Let’s get to the point. What surprised me is the difference in script execution speed between the first and second method.
    bearing in mind that the script processes several thousand lines, the first method takes about 7 and a half minutes, while the second method takes about 1 and a half minutes.

    A huge difference to get the same result.

    Could anyone tell me why?
    Thank you.

    Regards,
    Renato

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter guardiano78

    (@guardiano78)

    Maybe I understood. Through $args I retrieve much more information than the $wpdb query which only retrieves my id.
    Could this be the reason?

    Moderator bcworkz

    (@bcworkz)

    WP_User_Query only accepts certain orderby args. Even if you pass valid SQL, it may not be acceptable to the arg parser. There are some filters you could use to inject an arbitrary ORDER BY clause directly into the query’s SQL. There’s also a “fields” arg you can use to get the query to only return ID fields. If you did all of that, the time taken may be more similar since the resulting SQL will be much closer to yours in functionality.

    I think you’re right, the volume of data returned is likely the reason for the time discrepancy. You must have a huge user or user meta table for queries to take that long! The WP meta data schema isn’t very efficient to start with. If someone really needed more performative meta queries, they’d create a custom table where each column is a distinct meta value that would normally be the key name in the WP usermeta table.

    It’s clearly impractical to manage all meta data in this manner, but if the data is frequently used as criteria in large queries it’s better off in its own field.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Query Select … execution time’ is closed to new replies.