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` = #

Did you want to see all news articles and whether the user has voted on them? — Novikov, May 11 '11 at 02:01
@Novikov, yes by NULL field I meant a new which doesn't has any vote casted from this user or anybody. — Rifat, May 11 '11 at 03:14
I don't think your schema works, in the table votes you probably want the PK to be `hash, user_id` — Novikov, May 11 '11 at 06:39

Jason McCreary · Answer 1 · 2011-05-11T02:36:02.850

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.

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

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?

edited May 23 '17 at 12:04

Community

1
1

answered May 11 '11 at 01:54

Novikov

4,331
2
25
36

score 0 · Answer 3 · answered May 11 '11 at 02:00

0

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

answered May 11 '11 at 02:00

Steve Mallory

4,145
1
26
31

score 0 · Accepted Answer · answered Oct 09 '11 at 06:31

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` = #

Optimal Query for a problem

4 Answers4