Optimizing nested "Select Distinct" query

Question

I am using the following query which is causing issues. Note following points:

1. I am using 4 tables in this query:

   1. message_share
   2. messages
   3. myusertable
   4. friends

2. 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 <> 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?

Answer 1

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:

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

2. 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)

Answer 2

Non-standard GROUP BY

MySQL, with its default settings, is very lenient in interpreting queries that involve a GROUP BY clause, to the point where it can accept queries that make no sense at all. There is a server-wide ONLY_FULL_GROUP_BY setting that can be enabled to make MySQL follow the sane, standard behaviour.

Specifically, in both halves of the query, you have used GROUP BY with only one attribute (M.msg_id), but listed many columns to be selected without the use of aggregate functions. Assuming that msg_id is the primary key of the messages table, I could forgive the use of other columns of M in the SELECT list, such as M.uid_fk, M.message, etc. However, it makes no sense to select unaggregated columns of message_share and myusertable. Every SQL-compliant database would reject this query on those grounds.

I also do not understand what exactly you intend to accomplish with this query, and therefore cannot offer much advice in optimizing it.

OUTER JOIN

I am suspicious of your use of LEFT JOIN. I believe it is possible to obtain rows that consist entirely of NULL values. Don't write LEFT JOIN if you actually mean INNER JOIN.

UNION and LIMIT

It's not really possible to rewrite the UNION in a way that obtains exactly the same results, since you want each half of the query to return ten rows. If the row count didn't matter, you could extract some of the intermediate joining tables into a subquery.

DISTINCT

Using SELECT DISTINCT is almost always a sign that your joins are poorly formulated. Frequently, phantom rows appear when you perform JOINs when a WHERE EXISTS subquery might have been more appropriate. Unfortunately, I can't offer concrete advice since you didn't include any details about your schema in your question.