Need help in optimizing the query

Question

SELECT
    u.ID,
    u.display_name as name,
    u.user_email as email,
    u.user_registered as registered,
    (
        select
            meta_value
        from
            wp_usermeta
        where
            user_id = u.ID
            and meta_key = 'mobileno'
        limit
            1
    ) as mobileno,
    (
        select
            meta_value
        from
            wp_usermeta
        where
            user_id = u.ID
            and meta_key = 'referral_id'
        limit
            1
    ) as referral_id,
    (
        SELECT
            COUNT(meta_value) AS total_ref
        FROM
            wp_usermeta
        WHERE
            meta_key = 'ambassador_ref_id'
            AND meta_value = referral_id
    ) as total_ref,
    wc.task_no,
    wc.status,
    wc.uploaded_date,
    wc.reject_reason
FROM
    wp_users u,
    wp_ca_tasks wc
WHERE
    u.ID = wc.user_id
GROUP BY
    wc.user_id,
    wc.task_no;

In the above code, if we remove the block

(
            SELECT
                COUNT(meta_value) AS total_ref
            FROM
                wp_usermeta
            WHERE
                meta_key = 'ambassador_ref_id'
                AND meta_value = referral_id
        ) as total_ref

the code executes a bit faster. But if we add that block, it basically gets stuck in Loading...

Currently using MySQL 5.7.

How can I optimize the above block of code to make the execution faster?

Answer 1

Ah, the notorious WordPress meta-table slowdown.

Change the comma-joins (FROM a,b WHERE a.ID = b.user_id) to proper JOINs.

Eliminate your dependent subqueries and replace them with JOINed subqueries.

A quicker query might look like this.

SELECT
    u.ID,
    u.display_name as name,
    u.user_email as email,
    u.user_registered as registered,

    /* from the joined tables
    mobilno.meta_value as mobileno,
    referral_id.meta_value as referral_id,
    counts.total_ref

    wc.task_no,
    wc.status,
    wc.uploaded_date,
    wc.reject_reason
FROM
    wp_users u
    JOIN wp_ca_tasks wc ON u.ID = wc.user_id
    LEFT JOIN  wp_usermeta mobilno      ON mobilno.user_id = u.ID
                                       AND meta_key = 'mobilno'
   LEFT JOIN  wp_usermeta referral_id   ON referral_id.user_id = u.ID
                                       AND meta_key = 'referral_id'
   LEFT JOIN (
             SELECT COUNT(*) total_ref,
                    meta_value referral_id
               FROM wp_postmeta
              WHERE meta_key = 'ambassador_ref_id'
              GROUP BY meta_value)              
        ) counts ON  counts.referral_id = referral_id.meta_value
GROUP BY  wc.user_id, wc.task_no;

The trick is to avoid repeating the queries buried in the SELECT statement over and over. LEFT JOINing them helps.

And, your WordPress tables need better indexes. Look at this. https://wordpress.org/plugins/index-wp-mysql-for-speed/