MySQL Forums
Forum List  »  Performance

Optimizing a query with UNION
Posted by: Matthew Devine
Date: March 14, 2013 03:28PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing a query with UNION
3577
March 14, 2013 03:28PM
1209
March 14, 2013 09:08PM
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.