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 <> 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 <> 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 <> 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 <> 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)
$morequery
. It's also syntactically incorrect; you've got a danglingLIMIT
. I've taken the liberty of removing it, since leaving it in would make this question entirely off-topic. \$\endgroup\$