Optimizing nested "Select Distinct" query I am using the following query which is causing issues. 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 is to display messages, and share messages of users who are friend with each other.


This is similar to Facebook wall where they display friends' wall data.

The problem is only myusertable is causing the issue. It increase the load too much,  even to where the query can timeout.

When I remove myusertable from this query then it works fine, the use of myusertable is to fetch user full name. Everything else we are doing with userid and does not need  myusertable. For the sole purpose of getting user full name are we using myusertable.

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 &lt;&gt; 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)


Is there anything that I can do with myusertable to make this query faster?
Nitpicking
Aliases/notation
Single-letter alias names are not very helpful. Aliases should at least give Mr. Maintainer a clue what it stands for. What if you had a table called family along with friends... would you call it F2?
FROM friends F
LEFT JOIN message_share S ON S.ouid_fk &lt;&gt; 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'

Also, I find it good practice to use the optional AS keyword, just looks better in my opinion. How about:
FROM friends AS Frnd
LEFT JOIN message_share AS Share ON Share.ouid_fk &lt;&gt; Frnd.friend_two
LEFT JOIN messages AS Msg ON Msg.msg_id = Share.msg_id_fk AND Msg.uid_fk = Share.ouid_fk
LEFT JOIN myusertable AS User ON User.uid = Msg.uid_fk AND Usr.status1 = '1'

Column names
Some of your column names are pretty cryptic. I realize you may not have any say into it, but if this is your database you may consider changing names like ouid_fk and such to something more meaningful, if a bit more verbose.
Indentation/spacing
Use line breaks and indentation to facilitate reading of your queries. Also consistent spacing is an improvement.
(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

Why not instead:
(SELECT DISTINCT 
  Msg.msg_id, 
  Msg.uid_fk, 
  Msg.message, 
  Share.created, 
  Msg.like_count,
  Msg.comment_count,
  Msg.share_count, 
  Usr.username,
  Msg.uploads, 
  Share.uid_fk AS share_uid,
  Share.ouid_fk AS share_ouid

INT in a VARCHAR column?
I find this odd:
AND Usr.status1 = '1'

To me in a well-designed database, this status1 column should be int (4 bytes), smallint (2 bytes) or tinyint (1 byte). Not to mention, status1 is not a very good column name.
SELECT DISTINCT
I don't feel that this is needed at all, for a couple of reasons:

Do you expect multiple messages to have the same msg_id? Wouldn't that completely defeat the purpose of an ID?

Your GROUP BY msg_id already does this, but it does the work on the result set rather than the source data set, which will likely be much faster.


Here is what I would refactor to. However there is no way for me to test performance since you did not provide DDL or sample data.
(SELECT 
  Msg.msg_id, 
  Msg.uid_fk, 
  Msg.message, 
  Share.created, 
  Msg.like_count,
  Msg.comment_count,
  Msg.share_count, 
  Usr.username,
  Msg.uploads, 
  Share.uid_fk AS share_uid,
  Share.ouid_fk AS share_ouid
FROM friends AS Frnd
LEFT JOIN message_share AS Share ON Share.ouid_fk &lt;&gt; Frnd.friend_two
LEFT JOIN messages AS Msg ON Msg.msg_id = Share.msg_id_fk AND Msg.uid_fk = Share.ouid_fk
LEFT JOIN myusertable AS User ON User.uid = Msg.uid_fk AND Usr.status1 = '1'
WHERE Frnd.friend_one='199095' AND Frnd.role='fri'
GROUP BY Msg.msg_id
ORDER BY Share.created DESC LIMIT 10)
UNION
(SELECT 
  Msg.msg_id, 
  Msg.uid_fk, 
  Msg.message, 
  Share.created, 
  Msg.like_count,
  Msg.comment_count,
  Msg.share_count, 
  Usr.username,
  Msg.uploads, 
  Share.uid_fk AS share_uid,
  Share.ouid_fk AS share_ouid
FROM friends AS Frnd
LEFT JOIN message_share AS Share ON Share.ouid_fk &lt;&gt; Frnd.friend_two
LEFT JOIN messages AS Msg ON Msg.msg_id = Share.msg_id_fk AND Msg.uid_fk = Share.ouid_fk
LEFT JOIN myusertable AS User ON User.uid = Msg.uid_fk AND Usr.status1 = '1'
WHERE Frnd.friend_one='199095'
GROUP BY Msg.msg_id
ORDER BY Share.created DESC LIMIT 10)