Re: Query is occasionally sluggish

[Rick James]

> occasionally sluggish

As in, sluggish for certain values of userid and/or uid? Could it be that those values have more rows than the others?

> JOIN mentions m ON gq.id = m.questionId
> WHERE
> m.userId = 1

would benefit from
INDEX(userid, questionId)

Should the UNION be UNION ALL or UNION DISTINCT?

Try this: Have the UNION deliver only gq.tid and gq.id (= ga.qid). That is, don't touch group_answer until you get back from the UNION. (This implies no ORDER BY and LIMIT inside the inner SELECTs.)

Part of the sluggishness is hauling around the various fields of ta.

Also, because of these:
`answer` text,
`plaintext` text,
`data` text,
the temp tables cannot be MEMORY, but must be MyISAM.

By dealing with the TEXT fields in the outermost SELECT, you minimize the number of temp tables that need to be MyISAM, and minimize the bulk hauled around.