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.