Optimal Query for a problem

Question

I have a news table as following:

CREATE TABLE `news` (
  `title` varchar(255) NOT NULL,
  `hash` char(40) NOT NULL,
  PRIMARY KEY (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And, another votes table as following:

CREATE TABLE `votes` (
  `hash` char(40) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `vote_type` enum('up','down') DEFAULT NULL,
  PRIMARY KEY (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I want to get something like this when a user logged in-

this_is_a_title hash_12312312 NULL

this_is_a_title hash_12312313 up

this_is_a_title hash_12312314 NULL

this_is_a_title hash_12312315 down

Can you suggest an optimal single query for this?

I think my question was not clear. Sorry for that. By NULL field I meant a new which doesn't has any vote casted from this user or anybody.

---

My version is -

SELECT news.*, votes.vote_type
FROM news 
LEFT OUTER JOIN votes 
ON votes.`hash` = news.`hash` 
AND votes.`user_id` = #

Answer 1

I've compared with other's solution and so far I'm getting the best solution with this Query. I waited for over a month for any better solution.

SELECT news.*, votes.vote_type
FROM news 
LEFT OUTER JOIN votes 
ON votes.`hash` = news.`hash` 
AND votes.`user_id` = #

Answer 2

SELECT * FROM news JOIN votes ON news.hash = votes.hash WHERE votes.user_id = #;

This will SELECT all votes on news articles for a specific user with id #. The tables are joined with hash which is a primary key. So it's optimal given your current configuration. You could add an index for user_id for additional optimization.

Note: Since you are looking for optimal, I would encourage you not to use LEFT JOIN. Assuming news articles will grow over time, using a straight JOIN would limit the result set to only votes the user has cast. Which, on average, should be significantly smaller. However, you would need to compensate for this in your logic - i.e. the absence of a news record means no vote versus a NULL result.

Answer 3

Being that hash is the primary key of both tables, I believe you only need an INNER JOIN for an optimal query.

SELECT
    n.title,
    v.hash,
    v.vote_type
FROM
    votes v
    INNER JOIN news n USING(hash)
WHERE 
    v.user_id=123

Answer 4

SELECT news.title, news.hash, votes.vote_type 
FROM news 
LEFT JOIN votes USING(hash) 
WHERE votes.user_id = @my_user_id

Here's a good breakdown of different types of joins What is the difference between Left, Right, Outer and Inner Joins?