MySQL Forums
Forum List  »  Performance

Re: Optimizing a query with UNION
Posted by: Rick James
Date: March 14, 2013 10:41PM

That query seems identical in functionality to
            SELECT r.id AS replyId, r.replyText AS replyText
                FROM reply AS r
                JOIN question as q ON q.id = r.questionId
                JOIN member m1 ON q.groupId = m.groupId WHERE m.userId = {$userId} AND (m.status = 'admin' OR m.status = 'member')
                    
                UNION

                SELECT r.id AS replyId, r.replyText AS replyText
                    FROM reply AS r
                    JOIN question as q ON q.id = r.questionId
                    JOIN mentions m2 ON q.id = m.questionId
                    WHERE m.userId = {$userId}
ORDER BY 1 LIMIT 10;

Even better would be
          (  SELECT r.id AS replyId, r.replyText AS replyText
                FROM reply AS r
                JOIN question as q ON q.id = r.questionId
                JOIN member m1 ON q.groupId = m.groupId
               WHERE m.userId = {$userId} 
               AND (m.status = 'admin' OR m.status = 'member')
             ORDER BY 1 LIMIT 10
          )                    
                UNION
          (
                SELECT r.id AS replyId, r.replyText AS replyText
                    FROM reply AS r
                    JOIN question as q ON q.id = r.questionId
                    JOIN mentions m2 ON q.id = m.questionId
                    WHERE m.userId = {$userId}
             ORDER BY 1 LIMIT 10
         )
ORDER BY 1 LIMIT 10;

Looks like a typo -- perhaps you meant m1 and m2 instead of "m"??

After the second version,
reply: INDEX(replyId) may help.

Consider changing
`status` varchar(50) DEFAULT NULL,
to an ENUM. (Smaller -> more cacheable -> faster)
Ditto for `messageType` varchar(45) DEFAULT NULL,

In member, change
KEY `member_userId_idx` (`userId`),
to INDEX(userId, status, groupId)
(That will get "Using index".)

In mentions, change
KEY `mentions_userId_idx` (`userId`),
to INDEX(userId, questionId)
(MySQL won't use two indexes; a compound index is better.)

In the second select, you don't really use `questions`; remove it of justify its use.

If you still have issues, repost with those changes, and we can make another pass at it.

Options: ReplyQuote


Subject
Views
Written By
Posted
3578
March 14, 2013 03:28PM
1209
March 14, 2013 09:08PM
Re: Optimizing a query with UNION
1176
March 14, 2013 10:41PM
1302
March 15, 2013 11:32PM


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.