Optimizing a query with UNION
So I have the following query that is giving me fits. Performance is slowly getting worse as the table the number of rows that impact a user grows. Basically think of it as a discussion board system in a group settings, only members of the group can see the posts. However you have the ability to mention external users which would allow them to join in the conversation. This query is returning all the reply thread that the user is a member of the parent group or has been mentioned in.
Any help would be greatly appreciated.
******* QUERY
SELECT replyId, replyText
FROM (
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}
) as subquery
ORDER BY replyId DESC LIMIT 10;
***** Table Explanation
# Stores the replies to threads
CREATE TABLE `reply` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`questionId` int(11) unsigned NOT NULL,
`userId` int(11) unsigned NOT NULL,
`replyText` text,
PRIMARY KEY (`id`),
KEY `reply_questionId_idx` (`questionId`),
KEY `reply_userId_idx` (`userId`)
)
# Stores the users that are members of each group
CREATE TABLE `member` (
`groupId` int(11) unsigned NOT NULL,
`userId` int(11) unsigned NOT NULL,
`status` varchar(50) DEFAULT NULL,
PRIMARY KEY (`groupId`,`userId`),
KEY `member_userId_idx` (`userId`),
KEY `member_status_idx` (`status`)
)
# Stores the top level threads
CREATE TABLE `question` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(11) unsigned NOT NULL,
`groupId` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `question_userId_idx` (`userId`),
KEY `question_groupId_idx` (`groupId`)
)
# Stores the users mentioned and which question and/or reply they were mentioned within
CREATE TABLE `mentions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`messageType` varchar(45) DEFAULT NULL,
`messageId` int(10) unsigned NOT NULL,
`questionId` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mentions_unique_row` (`userId`,`messageType`,`messageId`),
KEY `mentions_userId_idx` (`userId`),
KEY `mentions_messageId_idx` (`messageId`,`messageType`),
KEY `mentions_questionId_idx` (`questionId`)
)
********Explain Statement
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11697 Using filesort;
2 DERIVED m1 ref PRIMARY,member_userId_idx,member_status_idx member_userId_idx 4 57 Using where
2 DERIVED q ref PRIMARY,question_groupId_idx question_groupId_idx 4 g.groupId 12 Using where; Using index
2 DERIVED r ref reply_questionId_idx reply_questionId_idx 4 gq.id 1
3 UNION m2 ref mentions_unique_row,mentions_userId_idx,mentions_questionId_idx mentions_unique_row 5 478 Using where
3 UNION r ref reply_questionId_idx reply_questionId_idx 4 m2.questionId 1 Using where
3 UNION q eq_ref PRIMARY PRIMARY 4 m2.questionId 1 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Edited 1 time(s). Last edit at 03/15/2013 08:58AM by Matthew Devine.