Mysql – Query or a New Column for Performance

MySQLmysql-5.6performancequery-performance

I have below tables:
ProductsTable (has product details),
UsersTable (has all application users),
RatingCategry (category for which user can rate a products like Quality, Cost etc.) and
UserRatingsforProductsTable (Users rating for a product for any RatingCategory) for a Mobile Application.

Products (ProductId, ProductName… other details like cost etc.)

UsersTable (UserId, UserName… other details like DateOfJoin etc.)

RatingCategory (RatingCategoryId, CategoryName)
**Data:**
1         Quality
2         Cost

UserRatingsforProductsTable (RatingId, UserId, ProductId, RatingCategoryId, Score)
**Data:**
1         5006  20  1   4.5

2         5006  20  2   5.0

3         5600  23  1   3.5

4         7800   20  1   2.5

5         8900   34  1   4.0

6         8900   34  2    4.5

What will be the best and faster query for a Mobile Application to show “Rating ScoreTotal” in the Products list (ProductId, ProductName, Cost, RatingScore)? Shall I do calculation in the Query or Add a new column in Products table that will be auto updated when each Rating added into UserRatingsforProductsTable? 

The expected output should be: 
ProductId, ProductName, Cost, OverallRatingForThisProd
---------  -----------  ----- ------------------------
*** OverAllRating = Average of Ratings from All Categories for the Specific Product from UserRatingsforProductsTable.

If the answer will be Query instead of adding a Column, can you please advise an optimized query?

Thank you.

Best Answer

I don't think adding a new column will be efficiently and you'll need to do more things like adding TRIGGERs to update the field and you'll get the newColumnvalue in every row for the table UserRatingsForProductsTable.

Information:

create Table Products(
  ProductId INT(4),
  ProductName VARCHAR(75),
  Cost DECIMAL(10,2),
PRIMARY KEY (`ProductId`));

CREATE TABLE UserRatingsForProductsTable(
  RatingId int(6) auto_increment,
  UserId int(5),
  ProductId int(4),
  RatingCategoryId int(2),
  Score decimal(5,2),
PRIMARY KEY (`RatingId`));

insert into Products select 20,'Product1',23.33;
insert into Products select 23,'Product2',223.33;
insert into Products select 34,'Product3',323.33;

insert into UserRatingsForProductsTable select 1,5006,20,1,4.5;
insert into UserRatingsForProductsTable select 2,5006,20,2,5.5;
insert into UserRatingsForProductsTable select 3,5600,23,1,3.5;
insert into UserRatingsForProductsTable select 4,7800,20,1,2.5;
insert into UserRatingsForProductsTable select 5,8900,34,1,4.0;
insert into UserRatingsForProductsTable select 6,8900,34,2,4.5;

Query:

SELECT
    p.ProductId,
    p.ProductName,
    p.Cost,
    ROUND(sum(URPT.Score)/(COUNT(DISTINCT URPT.RatingCategoryId)*COUNT(URPT.UserId)),1) as OverallRatingForThisProd
FROM Products as p
LEFT JOIN UserRatingsForProductsTable AS URPT ON (URPT.ProductId=p.ProductId)
GROUP BY p.ProductId
ORDER BY p.ProductId;

Try it on SQLFiddle.

Related Question