how to optimize the query

my current query is look like this:

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 = 'billingLas开发者_StackOverflow中文版tName'
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

this is giving me the correct result while i am checking in my localhost. but i want to display this in my browser. while i am trying to do this it is showing Fatal error: Maximum execution time of 30 seconds exceeded. but for small query i am getting result in my browser. can any one please optimize my query which i have given above.

---

You join on user_id and name again and again. You might be missing an appropriate index

CREATE INDEX uservar_user_id_name ON uservar (user_id, name);

But you would get much better performance if you create a table with the columns user_id, billingLastName, billingMobile, billingCity, billingState, billingAddress and billingFirstName instead of having them in separate rows in the uservar table.

---

You could put an index on userdb.uservar on the id and name columns since most of the joins are on these

---

Indexes might be of great help here if not already used.

---

You need an index on "uservar" (user_id, name)

You appear to be doing a potentially large crossjoin which could slow things down quite a bit. "userdb.user u,userdb.paymentgatewaytracker pg, oms.deliverymode od,oms.paymentmode op"

I suggest you post the "Explain Select ..." for the join.