How do I search for a value in a column and also get count of the column in a single SQL query

Question

I have a table of following structure in MySQL 5.7:

CREATE TABLE `post_like` (
  `post_title_id` varchar(255) NOT NULL,
  `user_name` varchar(50) NOT NULL,
  PRIMARY KEY (`post_title_id`,`user_name`),
) ENGINE=InnoDB;

and I have the following data

post_title_id    user_name

new-story        mani27
new-story        manipal
some-story       manipal

I am trying to get the count of likes for a particular story and also if a particular user has liked the story in a function likeStatus(user_name, post_title_id)

Suppose likeStatus(mani27, new-story) would result in:

count    status
2        1

I am using the following query right now and it works right:

SELECT COUNT(user_name) AS count,
COUNT(CASE WHEN `user_name` = ? THEN 1 ELSE null END) as status
FROM post_like WHERE post_title_id = ?

But this would execute the case function on all the rows in the table rather than searching the indexed column user_name.

I could use two different queries to get count and status of username liking a post, but that would take much more time than this. So, is there an optimised way to get this output?

Answer 1

I didn't check the query but this should give you an idea. Try Group By

SELECT COUNT(user_name) AS count,
COUNT(CASE WHEN `user_name` = ? THEN 1 ELSE null END) as status
FROM post_like GROUP BY post_title_id HAVING post_title_id=?

But this would execute the case function on all the rows in the table rather than searching the indexed column user_name

When you group by basing on post_title_id= and then applying count functions on them, number of row searches for username can be reduced to rows in that group