Mysql Putting it inside a select will make it slower-CodePudding

I had a question while writing a query using mysql. So I searched it, but couldn't find an answer, so I write a question.


SELECT CAT.ID, CAT2.BIRTH, CAT.NAME
FROM CAT
INNER JOIN CAT2 ON CAT.ID = CAT2.CATID
GROUP BY CAT.ID, CAT2.BIRTH, CAT.NAME

0.032Sec

SELECT * FROM (
  SELECT CAT.ID, CAT2.AGE, CAT.NAME
  FROM CAT
  INNER JOIN CAT2 ON CAT.ID = CAT2.CATID
  GROUP BY CAT.ID, CAT.Birth, CAT.NAME
) AS AA

2.16Sec

I just wrapped it in select, but I don't know why it's so slow.

When I checked with explain, the indexes ( CAT.ID, CAT.Birth, CAT.NAME) are working fine.

CodePudding user response：

In the second query, MySQL will place the result of the subquery into memory. It then must query that data again, which takes more time. You would likely see an even larger split in performance if you were filtering in the outer query. Note that any index can only be used inside the subquery, but not on the outer query, which generally must be scanned.