MySQL Forums
Forum List  »  Performance

Query is occasionally sluggish
Posted by: Matthew Devine
Date: July 18, 2013 01:12PM

So the query below is used to retrieve a list of response to posted questions that the user has access to. Its broken up into two parts, the first has to do with groups the users is a member within; while the second links users to groups that they were pulled into to.

This query is used to refresh a timeline and its been getting sluggish over the past month. It has started showing up in the slow query log but it isn't always slow. Any ideas on what could be happening?

Thanks Matt

Offending Query

SELECT
*
FROM
((SELECT
ga.id AS replyId,
ga.uid AS userId,
ga.answer as message,
ga.plaintext as plaintext,
ga.created as created,
ga.data as data,
gq.tid as yardId,
gq.id as questionId,
ga.pid as parentReplyId,
FROM
group_answer AS ga
JOIN group_question as gq ON gq.id = ga.qid
JOIN group_member g ON gq.tid = g.tid
WHERE
g.uid = 1
AND (g.status = 'admin'
OR g.status = 'member')
ORDER BY ga.id DESC
LIMIT 0 , 10) UNION (SELECT
ga.id AS replyId,
ga.uid AS userId,
ga.answer as message,
ga.plaintext as plaintext,
ga.created as created,
ga.data as data,
gq.tid as yardId,
gq.id as questionId,
ga.pid as parentReplyId,
FROM
group_answer AS ga
JOIN group_question as gq ON gq.id = ga.qid
JOIN mentions m ON gq.id = m.questionId
WHERE
m.userId = 1
ORDER BY ga.id DESC
LIMIT 0 , 10)) AS s
ORDER BY replyId DESC
LIMIT 0 , 10;

+------+--------------+------------+----------+-------------------------------------------------------------------------+-----------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+----------+-------------------------------------------------------------------------+-----------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | Using filesort |
| 2 | DERIVED | g | range | PRIMARY,gm_tid_status_idx,gm_uid_status_tid_idx | gm_uid_status_tid_idx | 5 | NULL | 159 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 2 | DERIVED | gq | ref | PRIMARY,gq_tid_idx | gq_tid_idx | 4 | g.tid | 8 | 100.00 | Using where; Using index |
| 2 | DERIVED | ga | ref | ga_qid_idx | ga_qid_idx | 4 | gq.id | 2 | 100.00 | |
| 3 | UNION | m | ref | unique_row,qid_idx,uid_qid_type_idx | unique_row | 5 | | 761 | 100.00 | Using where; Using temporary; Using filesort |
| 3 | UNION | gq | eq_ref | PRIMARY | PRIMARY | 4 | m.questionId | 1 | 100.00 | Using where |
| 3 | UNION | ga | ref | ga_qid_idx | ga_qid_idx | 4 | m.questionId | 2 | 100.00 | Using where |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+----------+-------------------------------------------------------------------------+-----------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+

+------------+---------------+------+-----+---------+----------------+

CREATE TABLE `group_answer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) DEFAULT '0',
`tw_id` bigint(20) DEFAULT NULL,
`qid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`tw_uid` int(20) DEFAULT NULL,
`tw_info` longtext,
`answer` text,
`plaintext` text,
`created` int(11) DEFAULT NULL,
`data` text,
PRIMARY KEY (`id`),
KEY `groupanswer_qid_idx` (`qid`),
KEY `groupanswer_pidqid_idx` (`pid`,`qid`),
KEY `groupanswer_uid_idx` (`uid`),
KEY `groupanswer_created_idx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+------------+---------------+------+-----+---------+----------------+

CREATE TABLE `group_question` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`qid` int(11) NOT NULL,
`type` varchar(50) DEFAULT NULL,
`tw_id` bigint(20) DEFAULT NULL,
`tid` int(11) NOT NULL,
`fid` int(11) DEFAULT NULL,
`uid` int(11) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`question` text,
`created` int(11) DEFAULT NULL,
`view` text,
`data` text,
`subject` varchar(75) NOT NULL DEFAULT '',
`plaintext` text,
PRIMARY KEY (`id`),
KEY `gq_qid_idx` (`qid`),
KEY `gq_uid_idx` (`uid`),
KEY `gq_tid_idx` (`tid`,`created`),
KEY `gq_created_idx` (`created`),
KEY `gq_fid_idx` (`fid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+------------+---------------+------+-----+---------+----------------+

CREATE TABLE `group_member` (
`tid` int(10) unsigned NOT NULL DEFAULT '0',
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`status` enum('member','admin','invited','pending') NOT NULL,
`data` longtext,
`time` int(10) unsigned DEFAULT '0',
`lastAccess` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`tid`,`uid`),
KEY `gm_lastAccess_idx` (`lastAccess`),
KEY `gm_tid_status_idx` (`tid`,`status`),
KEY `gm_uid_status_tid_idx` (`uid`,`status`,`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+------------+---------------+------+-----+---------+----------------+

CREATE TABLE `mentions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userId` int(11) DEFAULT NULL,
`messageType` enum('question','reply','wallpost') NOT NULL,
`messageId` int(10) unsigned NOT NULL,
`questionId` int(10) unsigned NOT NULL,
`viewedTime` int(10) unsigned NOT NULL,
`repliedTime` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_row` (`userId`,`messageType`,`messageId`),
KEY `messageId_idx` (`messageId`,`messageType`),
KEY `questionId_idx` (`questionId`),
KEY `userId_questionId_type_idx` (`userId`,`messageId`,`messageType`)
) ENGINE=InnoDB AUTO_INCREMENT=5468 DEFAULT CHARSET=utf8;

+------------+---------------+------+-----+---------+----------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Query is occasionally sluggish
1871
July 18, 2013 01:12PM


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.