mysql - 查询优化

Question

我需要来自不同 4 个类别的最新 4 个产品。该查询不起作用

SELECT 
  ProductID,ProductName,thumb
  FROM 
  tbproduct 
  WHERE 
  status =2 
GROUP BY 
`CATEGORYID` 
ORDER BY 
`ProductID` DESC 
LIMIT 4

所以我首先按所有记录排序。并使用以下查询...

SELECT 
ProductID, ProductName, thumb 
FROM 
  (
  SELECT 
  ProductID,ProductName,thumb,CATEGORYID 
  FROM 
  tbproduct 
  WHERE 
  status =2 
  ORDER BY 
  `ProductID` DESC
  ) AS tmp 
GROUP BY 
`CATEGORYID` 
ORDER BY 
`ProductID` DESC 
LIMIT 4

这个查询很慢，有人可以建议我它的快速查询吗？我正在使用 MySQL。

Answer 1

如果您只是使用:

SELECT 
  ProductID,ProductName,thumb,CATEGORYID 
  FROM tbproduct 
  WHERE status=2
  GROUP BY CATEGORYID
  ORDER BY ProductID DESC
  LIMIT 4