如何优化查询

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

当我签入我的localhost时，这给了我正确的结果。但我想在我的浏览器中显示这个。当我尝试这样做的时候，它显示了致命错误:超过了30秒的最大执行时间。但是对于小查询，我会在浏览器中得到结果。谁可以请优化我的查询，我已经在上面给了。

Answer 1

你在user_id上一次又一次地加入并命名。您可能缺少适当的索引

CREATE INDEX uservar_user_id_name ON uservar (user_id, name);

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

Answer 2

您可以在id和name列的userdb.uservar上建立索引，因为大多数连接都在这两列上

Answer 3

如果还没有用过，Indexes在这里可能会有很大的帮助。