Here’s the table structure:
CREATE TABLE songs
(
song_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE ratings
(
song_id INT UNSIGNED NOT NULL DEFAULT '0',
user_id INT UNSIGNED NOT NULL DEFAULT '0',
rating TINYINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (song_id, user_id),
INDEX (user_id)
);
CREATE TABLE bookmarks
(
song_id INT UNSIGNED NOT NULL DEFAULT '0',
user_id INT UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (song_id, user_id),
INDEX (user_id)
);
Here’s the sql code:
SELECT
songs.name,
average_rating,
total_ratings,
total_bookmarks
FROM
songs
INNER
JOIN
(
SELECT
song_id,
AVG(rating) AS average_rating,
COUNT(*) AS total_ratings
FROM
ratings
GROUP
song_id
) AS ratings_summary
ON ratings_summary.song_id = songs.song_id
LEFT
JOIN
(
SELECT
song_id,
COUNT(*) AS total_bookmarks
FROM
bookmarks
GROUP BY
song_id
) AS bookmarks_summary
ON bookmarks_summary.song_id = songs.id
ORDER BY
total_ratings DESC;
The above query works, but it takes 5 seconds. If I remove the ORDER BY it takes one-tenth of a second. If it remove either one of the sub-queries (derived tables) it also takes one-tenth of a second.
This is really killing me. I’ve done searches about COUNT, GROUP BY, but there aren’t many examples of getting proper counts when joining multiple one-to-many tables.
Using COUNT with GROUP BY is fine if you’re only joining two tables, but once you add a second one-to-many table to the mix, it throws all the aggregate functions out of whack because it causes the extras rows to multiple against each other.
Instead of using derived tables I could use traditional joins directly against the tables, but then I’d have to resort to using sloppy COUNT(DISTINCT song_id) which works around it, but it’s just as slow as the 5 second monster above. I’m really at my wits end here.
The situation gets even worse because I’ll actually need to add more one-to-many joins which’ll make it even slower.