I'm making a website where I display questions and answers asked by users.

My problem is every time I want to display a question I have to select the question & the user & sum of votes & userid who voted (to check if the logged in user already voted)

So I'm using 4 queries to get the question and user and number of votes & check if question has already been liked/disliked and I still need to add answers too so that's 4 more queries for each answer. Is there a way to speed it up?

PS: I'm using php & mysqli to display data

I chose my sql tables to be :

question {
 id (primary key),
 title,
 date,
 user_id (foreign key) (added index) (to see who asked the question)
}

vote {
 id (primary key),
 type (like(=1) or dislike(=-1)),
 user_id (to see which user voted),
 question_id (foreign key) (added index)
}

user{
 id (primary key),
 username,
 password
}

Is there a way to adjust my tables for better speed?

CodePudding user response：

You don't have to change your tables.

Join the aggregated votes to your question table. You can use conditional aggregation to see whether the user voted, too.

select *
from question q
left join
(
  select
    question_id,
    sum(type) as cumulated_vote,
    sum(when user_id = current_user then type end) as own_vote
  from vote
  group by question_id
) v on v.question_id = q.id

Replace current_user with the variable or function your DBMS uses to show the current user.