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