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;
在上面的代碼中，如果我們洗掉塊
(
SELECT
COUNT(meta_value) AS total_ref
FROM
wp_usermeta
WHERE
meta_key = 'ambassador_ref_id'
AND meta_value = referral_id
) as total_ref
代碼執行得更快一些。但是如果我們添加那個塊，它基本上會卡在加載中......
目前使用 MySQL 5.7。
如何優化上述代碼塊以加快執行速度？
uj5u.com熱心網友回復：
啊，臭名昭著的WordPress元表減速。
將逗號連接 ( FROM a,b WHERE a.ID = b.user_id) 更改為正確的 JOIN。
消除您的依賴子查詢并將它們替換為 JOINed 子查詢。
更快的查詢可能如下所示。
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;
訣竅是避免一遍又一遍地重復埋在 SELECT 陳述句中的查詢。左加入他們有幫助。
而且，您的 WordPress 表需要更好的索引。看這個。https://wordpress.org/plugins/index-wp-mysql-for-speed/
uj5u.com熱心網友回復：
除了 O.Jones 所說的，wc需要
INDEX(user_id, task_no)
但是，GROUP BY可能違反了“only_full_group_by”。也就是說，對于給定的user_idand task_no，您將獲得從 獲取的其他列的隨機值wp_ca_tasks。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/418953.html
標籤：