Performance of summing one value from join of three tables

Question

I have a table I want to get a sum of information on, but the value I need to query on is in two different tables.

The one with values I need to sum is say, kudos, which has a value indicating +1 (1) or -1 (2) in the Action column, which is associated to the discussion it's on, or the comment it's on:

Kudos
+-----------+--------------+--------+
| CommentID | DiscussionID | Action |
+-----------+--------------+--------+
|        24 |         NULL |      1 |
|      NULL |            4 |      1 |
|        60 |         NULL |      2 |
+-----------+--------------+--------+

The value I want to filter by is the author's ID, which is either in the Comment table or the Discussion table:

Comments
+-----------+--------+
| CommentID | Author |
+-----------+--------+
|        29 |      5 |
|        24 |      1 |
|        22 |      1 |
|        21 |      1 |
+-----------+--------+

Discussions
+--------------+--------+
| DiscussionID | Author |
+--------------+--------+
|            4 |      1 |
|            5 |      2 |
|            6 |      2 |
|            7 |      7 |
+--------------+--------+

In this example, Author 1 would have a score of 2, because row 1 in the Kudos table would count because it matches with row 1 in the comment table, and row 2 in the Kudos table matches the 1st row in the Discussions table.

Additionally, I need to use the query that does this as a subquery, because I'm getting all the users in a select statement, and then getting the sum of this subquery as a field associated with the userID, like:

SELECT tblUser.Name AS Name, (subquery) AS rep FROM User_Table AS tblUser; 

What I've come up with is the following:

SELECT tblU.Name AS Name, tblU.UserID AS ID,
    (SELECT SUM(IF(Action=1, 1, -1)) FROM Kudos AS tblK 
    LEFT JOIN Discussions AS tblD ON tblK.DiscussionID = tblD.DiscussionID 
    LEFT JOIN Comments AS tblC ON tblC.CommentID = tblK.CommentID 
    WHERE tblD.InsertUserID = id OR tblC.InsertUserID = id) AS rep 
FROM Users AS tblU;

which gives me:

+--------------+----+------+
| Name         | id | rep  |
+--------------+----+------+
| Jack         |  1 |  357 |
| Joe          |  2 |  824 |
| Jim          | 12 |   48 |

But it takes 0.23 seconds to run for a small set (like 13 users). I've heard from co-workers that the OR condition is bad for performance, but I'm not sure how this can be optimized at all.

Answer 1

Avoid LEFT JOIN clauses

I'm not sure that I'd pick the OR as the problem. The real problem is more likely to be the LEFT JOIN clauses. A LEFT JOIN is a slow operation and to be avoided if you aren't looking for NULL values.

Try using two subselects instead:

SELECT tblU.Name AS Name, tblU.UserID AS ID,
  (SELECT SUM(IF(Action=1, 1, -1))
   FROM Kudos AS tblK
     INNER JOIN Discussions AS tblD ON tblK.DiscussionID = tblD.DiscussionID
   WHERE tblD.InsertUserID = tblU.UserID) +
  (SELECT SUM(IF(Action=1, 1, -1))
   FROM Kudos AS tblK
     INNER JOIN Comments AS tblC ON tblC.CommentID = tblK.CommentID 
   WHERE tblC.InsertUserID = tblU.UserID ) AS rep
FROM Users AS tblU;

Note that this only works if Kudos never has a non-null CommentID in a row with a non-null DiscussionID. This also has the side effect of getting rid of the OR, but I'm not sure that's what matters.

Use indexes

Another thing that you can do is add indexes on the relevant columns if you don't already have them. Hopefully you already have PRIMARY KEY indexes on the ID column for each table. If that's not enough, it is possible to add indexes on things like UserID, Name and DiscussionID, InsertUserID, Action. These can move critical columns into memory.

Consider merging Comments and Discussions

The Comments and Discussions tables use the same columns here. Consider putting both in the same table with a type column. Then Kudos would have to do only one join.

You don't include the various table definitions. If there's too much stuff in Comments and Discussions for a simple merge, you could also consider refactoring them into three tables. The ID column and author could be set in one table while the other two tables could hold the comment and discussion specific content respectively. I would consider a merge to be a better solution than this if possible. This is a backup suggestion.