Is it possible to perform two checks in the same query in one traversal?

Posted on

Question :

I’m working on a forum project using PHP/Laravel and MySql where I have 3 models : User, Thread, Like (users, threads, likes tables).

In index page, I display thread components, and each component calculate the number of votes associated to thread, as well as checking If the current authenticated user already vote this thread or not.

When I check the generated queries in the debugger, I get 2 similar queries for each component; One to traverse the entire votes table to get the number of votes associated to thread, and the other query check If the current user already vote the thread or not which also traverse the whole table in worst case:

1:

select count(*) as totalthreadlikes 
  from `likes` 
 where `likes`.`likable_id` = 283 
   and `likes`.`likable_type` = 'AppModelsThread'

2:

select count(*) as threadliked 
  from `likes` 
 where `likes`.`likable_id` = 283 
   and `likes`.`likable_type` = 'AppModelsThread' 
   and `user_id` = 1

The two queries are very similar except the second one add a condition to check if the auth user has a vote on this thread. As you can see, the app traverse likes table twice, and because likes table is very huge, this affect the performence of my application and make it very slow.
My question is : Is there any way to traverse likes table only once and count the number of likes records associated to the thread and at the same traverse check if the current user has a record there.

These two queries are generated from ORM that I’m using right now, and I decided to use raw queries If that is possible.

Any advice would be appreciated. Thanks

Answer :

SELECT  COUNT(*) AS totalthreadlikes,
        SUM(user_id = 1) AS threadliked
    WHERE  likeable_id = 283
      AND  likable_type = 'AppModelsThread'

(You may need to double up those backslashes.)

SUM(boolean) counts how many cases are “true”.

The optimal index: INDEX(likeable_id, likable_type, user_id) — the two columns used in the WHERE need to come first, unlike another Answer.

You need a multicolumn index for table lookout by several conditions at the same time. I can’t be more specific without table likes definition but I suspect you need an additional index like that:

(user_id, likable_id, likable_type)

After days of search and asking people, I found a workaround by myself to get both thread likes count as well as whether the current user liked that thread or not in only one query, and that by querying the likes table and get user_id like so:

SELECT user_id FROM likes WHERE likable_type='AppModelsThread' AND likable_id=283

After, to get the number of likes, I only need to get the array length of the result, and to check whether the user liked it or not, to check the user id if it exists in the result array of ids and all of that is done in one query.

As a conclusion, I was limiting my thought by thinking that all checks should be done in DB level, but I realised after that, that sometimes it is better to get the result from only one traversal in db level, and take that result and do my other checks using a server side programming language (because checking a result of 100 records is easier than do double check in a table with millions of records).

Thanks for everyone !

Leave a Reply

Your email address will not be published. Required fields are marked *