Two tables for storing comments

Question

I have two tables for storing comments

comments

CREATE TABLE `comments` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `text` text NOT NULL,
 `author` int(10) unsigned NOT NULL,
 `time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

comments_ratings

CREATE TABLE `comments_ratings` (
 `comment` int(10) unsigned NOT NULL,
 `user` int(10) unsigned NOT NULL,
 `vote` tinyint(4) NOT NULL,
 PRIMARY KEY (`comment`,`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

users

CREATE TABLE `users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

The goal is to select comments along with rating and information about the author as well as whether the logged in user has voted or not and if yes their vote's value.

SELECT 
c.id, 
c.text, 
c.time, 
c.author, 
u.email AS author_name, 
SUM(cr.vote) AS rating,
MAX(if(cr.user = ?, cr.vote, NULL)) AS voted

FROM comments c 
LEFT JOIN users u ON u.id = c.author 
LEFT JOIN comments_ratings cr ON c.id = cr.comment

GROUP BY c.id
ORDER BY c.id DESC

I used to do a second join on comments_ratings in order to get the vote of the logged in user but I recently switched to this thing that I'm not fully sure on to how it works, I just have my guesses. I wrote it with the help of another question I asked and some luck.

Ultimately what I want to ask is, are there any possible issues I can run into with

MAX(if(cr.user = ?, cr.vote, NULL)) AS voted

And I'm asking performance-wise. I know it may not be very easy to read but all I care about now is performance. Is this alright or I should definitely switch back to an additional join on commments_ratings like LEFT JOIN comments_ratings cr2 ON c.id = cr2.comment AND c.author = cr2.user to get the value of vote?

Answer 1

Disclaimer: I work with MSSQL most of the time, so my syntax might be a bit off.

Looking at your code : as you do GROUP BY c.id you will get 1 record per comment. (**) Each line will get the total sum of cr.vote values found for each comment. So far so good.

But then it gets a bit muddy. It seems you want to add the author-information too, but you use a LEFT OUTER JOIN, meaning that you expect/prepare for some comments to have an author id that is not present in the users table?! Is this by design? If not I'd suggest to change the OUTER JOIN into an inner JOIN and additionally to put a FOREIGN KEY from the author field towards the users-id field. This way you prevent 'bad data' and it also tells the RDBMS that there always will be a matching record.

In MSSQL, a SUM() or MAX() on purely NULLs (= what will get returned from the LEFT OUTER JOIN in case no matching comments_ratings records exist) will return a NULL. You seem to try to catch this by using the IF() construction but I'd rather suggest to a 'COALESCE()` with some default value, probably zero.

As such you'd get:

SELECT c.id, 
       c.text, 
       c.time, 
       c.author, 
       u.email AS author_name, 
       COALESCE(SUM(cr.vote), 0) AS rating,
       COALESCE(MAX(cr.vote), 0) AS voted

  FROM comments c 
 JOIN users u ON u.id = c.author  
 LEFT OUTER JOIN comments_ratings cr ON c.id = cr.comment
GROUP BY c.id, c.text, c.time, c.author, u.email

**: As you can see I've added all fields that are not aggregated to the GROUP BY clause, in MSSQL this is a requirement for the syntax to be valid; I believe this is not the case for mysql, but I see it as a good habit in all cases. (YMMV =)