MySQL Forums
Forum List  »  Optimizer & Parser

Help me to optimize my sql query
Posted by: GAURAV JAIN
Date: August 21, 2014 07:29AM

I am using following query which causing issue. Note following points

I am using 4 tables in this query

message_share messages myusertable friends

What i am trying to do in this query

I am trying to display messages, share messages of users who are friend with each other.

This is similar to fb wall where they display friends wall data

the problme is only myusertable causing the issue. it increase too much load even query timeout

when i remove myusertable from this query then work fine, the use of myusertable is to fetch user full name. rest we are doing with userid and which need not myusertable. Because of getting user full name we are using the myusertable only

The query is

(SELECT DISTINCT M.msg_id, M.uid_fk, M.message, S.created, M.like_count,M.comment_count,M.share_count, U.username,M.uploads, S.uid_fk AS share_uid,S.ouid_fk AS share_ouid
FROM friends F
LEFT JOIN message_share S ON S.ouid_fk <> F.friend_two
LEFT JOIN messages M ON M.msg_id = S.msg_id_fk AND M.uid_fk = S.ouid_fk
LEFT JOIN myusertable U ON U.uid = M.uid_fk AND U.status1='1'
WHERE F.friend_one='199095' AND F.role='fri'
GROUP BY msg_id
ORDER BY created DESC LIMIT 10)
UNION
(SELECT DISTINCT M.msg_id, M.uid_fk, M.message, M.created, M.like_count,M.comment_count,M.share_count, U.username,M.uploads, '0' AS share_uid, '0' AS share_ouid
FROM friends F
LEFT JOIN messages M ON M.uid_fk = F.friend_two
LEFT JOIN myusertable U ON U.uid = M.uid_fk AND U.status1='1'
WHERE F.friend_one='199095'
GROUP BY msg_id
ORDER BY created DESC LIMIT 10)

Options: ReplyQuote


Subject
Views
Written By
Posted
Help me to optimize my sql query
2329
August 21, 2014 07:29AM
1156
August 22, 2014 06:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.