0

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` = 'App\Models\Thread'

2:

select count(*) as threadliked 
  from `likes` 
 where `likes`.`likable_id` = 283 
   and `likes`.`likable_type` = 'App\Models\Thread' 
   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

2
2
SELECT  COUNT(*) AS totalthreadlikes,
        SUM(user_id = 1) AS threadliked
    WHERE  likeable_id = 283
      AND  likable_type = 'App\Models\Thread'

(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.

0

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)
1
  • Move the WHERE columns to the front of the index.
    – Rick James
    Sep 21 '21 at 0:26
-1

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='App\Models\Thread' 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 !

2
  • I don't know why this reply is downvoted ! what's wrong here ! my intention is to share share what I've learned from my days of search and testing. this is not only here but also in stackoverflow as well, my goal is share something useful or ask a question but All what I deserve is downvotes and negative feedbacks ! At least this man who downvoted this reply leave a comment to clarify Sep 21 '21 at 20:07
  • (Some people here seem to be quick to down-vote.) A guess: Your Question asked for a single query that produced a count and a true/false value with minimal effort. Your self-Answer takes 3 steps: one SQL, then 2 (easy) array operations. So, the down-voter perhaps judged your answer to be no better than the original 2-SQL version.
    – Rick James
    Jan 10 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.