mysql - 如何优化查询

Question

我当前的查询如下所示:

SELECT distinct pg.id,pg.email,pg.theme,op.PaymentMode,od.DeliveryMode,uv1.value AS FirstName, uv2.value AS LastName, uv3.value AS Mobile, uv4.value AS City, uv5.value AS State, uv6.value AS Address
FROM userdb.user u,userdb.paymentgatewaytracker pg,
oms.deliverymode od,oms.paymentmode op,
userdb.uservar uv1
JOIN userdb.uservar uv2 ON uv1.user_id = uv2.user_id
AND uv2.name = 'billingLastName'
LEFT JOIN userdb.uservar uv3 ON uv1.user_id = uv3.user_id
AND uv3.name = 'billingMobile'
JOIN userdb.uservar uv4 ON uv1.user_id = uv4.user_id
AND uv4.name = 'billingCity'
JOIN userdb.uservar uv5 ON uv1.user_id = uv5.user_id
AND uv5.name = 'billingState'
JOIN userdb.uservar uv6 ON uv1.user_id = uv6.user_id
AND uv6.name = 'billingAddress'
where uv1.name = 'billingFirstName'
AND u.id = uv1.user_id
AND u.email=pg.email
and op.PaymentModeId=pg.paymethod
and od.DeliveryModeId=pg.deliveryOption
ORDER BY pg.id

当我检查本地主机时，这给了我正确的结果。但我想在我的浏览器中显示它。当我尝试执行此操作时，它显示 fatal error :超出了最大执行时间 30 秒。但对于小查询，我在浏览器中得到结果。任何人都可以优化我上面给出的查询吗？

Answer 1

您一次又一次地加入 user_id 和 name。您可能缺少适当的索引

CREATE INDEX uservar_user_id_name ON uservar (user_id, name);

但是，如果您创建一个包含 user_id、billingLastName、billingMobile、billingCity、billingState、billingAddress 和 billingFirstName 列的表，而不是将它们放在 uservar 表中的单独行中，则会获得明显更好的性能。