Re: Optimizing a query with UNION

[Matthew Devine]

So I went ahead and looked at and used your "even better" query. On top of adding the following indices.

Member Table
ADD INDEX `member_tid_status_idx` (`tid` ASC, `status` ASC)
ADD INDEX `member_uid_status_tid_idx` (`uid` ASC, `status` ASC, `tid` ASC)

Mention Table
ADD INDEX `userId_questionId_type_idx` (`userId` ASC, `messageId` ASC, `messageType` ASC)

I also went ahead and made those ENUM changes because they just made sense. It definitely runs faster, what kinds of things do I need to look at to try and eliminate either the temporary tables or Filesorts? Below is the new explain post changes.


1, PRIMARY, m1, range, PRIMARY,member_tid_status_idx,member_uid_status_tid_idx, member_uid_status_tid_idx, 5, , 56, Using where; Using index; Using temporary; Using filesort
1, PRIMARY, q, ref, PRIMARY,question_tid_idx, question_tid_idx, 4, examyard.m1.tid, 7, Using where; Using index
1, PRIMARY, r, ref, reply_qid_idx, reply_qid_idx, 4, examyard.q.id, 1,
2, UNION, m2, ref, unique_row,questionId_idx,userId_questionId_idx, userId_questionId_idx, 5, const, 479, Using where; Using index; Using temporary; Using filesort
2, UNION, q, eq_ref, PRIMARY, PRIMARY, 4, examyard.m2.questionId, 1, Using where
2, UNION, r, ref, reply_qid_idx, reply_qid_idx, 4, examyard.m2.questionId, 1, Using where
, UNION RESULT, <union1,2>, ALL, , , , , , Using filesort