CODEWITHSUNDEEP
phpmysqlsql
Steve Donnelly
Steve Donnelly

Reputation: 1

Extremely slow PHP mySQL query on movies database

I have a php file querying a mySql database set up with movies on one table and movieRatings on another. I want to count the number of "5 star", "4 star", etc ratings that a particular movie has. My query works, however it is very slow as I realize it goes through the entire movieRatings table 5 times for each movie I want to look up.

My query:

$sql = "SELECT * 
                FROM MOVIE_INFO,
                (SELECT count(ratingValue) as star5 FROM MOVIE_RATINGS2 WHERE itemID=$b[0] AND ratingValue = 5 ) SUB,
                (SELECT count(ratingValue) as star4 FROM MOVIE_RATINGS2 WHERE itemID=$b[0] AND ratingValue = 4 ) SUB1,
                (SELECT count(ratingValue) as star3 FROM MOVIE_RATINGS2 WHERE itemID=$b[0] AND ratingValue = 3 ) SUB2,
                (SELECT count(ratingValue) as star2 FROM MOVIE_RATINGS2 WHERE itemID=$b[0] AND ratingValue = 2 ) SUB3,
                (SELECT count(ratingValue) as star1 FROM MOVIE_RATINGS2 WHERE itemID=$b[0] AND ratingValue = 1 ) SUB4

                WHERE movieID = $b[0]";

I figure if I can pull the subset from the movie ratings for that particular movie once, it would speed it up immensely, but I can't remember how to do that.

Upvotes: 0

Views: 175

Answers (1)

Eric J.
Eric J.

Reputation: 150108

You are performing multiple sub-selects. Instead, try a grouping query, e.g.

SELECT ratingValue, COUNT(ratingValue) FROM MOVIE_INFO
WHERE movieID = $b[0]
GROUP BY ratingValue

You will get back one row per star category. The first column will be the rating itself (e.g. 1) and the second column will be the number of ratings with that number of stars.

Upvotes: 3

Related Questions