CODEWITHSUNDEEP
mysqloptimization
Deepa
Deepa

Reputation: 1301

query optimization

I need newest 4 product from different 4 categories. this query is not working

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

so i first order by all records.and use following query...

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

this query is slow can any one suggest me its fast query. I a using MySQL.

Upvotes: 0

Views: 93

Answers (3)

Pomyk
Pomyk

Reputation: 3328

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

That should do.

Upvotes: 0

Nishant
Nishant

Reputation: 55866

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

Does this not work?

Not sure if this will help, see the updated query. See usage of Group-by Aggragate.

Note: This will not reliably return other columns. Please look here for left-self-exclusion join

Upvotes: 0

Marco
Marco

Reputation: 57573

If you simply use:

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

Upvotes: 1

Related Questions