database - mysql count votes optimization -

so im making file hub nothing huge or fancy store files may shared others download. , occured me in way intended count amount of upvotes or downvotes query server heavy.the query files along lines of

select*from files; 

and in such recieve array of files loop on , specifics on each file inclusion of voting file same foreach loop include further query count amount votes file (the file id in clause) so

select*from votes upvoted=true , file.id=? 

and thinking of using pdo::rowcount answer. evey bone in body says bad bad imagine im getting 10,000 files ran 10,000 queries 1 on each file , havent looked @ downvotes yet think go in similar fasion. optimization adviece here small rep of structure of few tables. upvoted , downvoted columbs of type bool or tinyint if will

   table: file                table: user                table: votes        +----+-------------+    +----+-------------+ +--------+--------+--------+--------+ | id |storedname   |    | id | username    | |file_id | user_id| upvoted | downvoted +----+-------------+    +----+-------------+ +--------+--------+--------+--------+  | 1  | 45tfvb.txt  |    | 1  | matthew     | | 1      |  2     |    1   |      0 | 2  |jj7fnfddf.pdf|    | 2  | mark        | | 2      |  1     |    1   |      1 | .. | ..          |    | .. | ..          | | ..     |  ..    |    ..  |      .. 

there 2 ways this. better way (aka faster) write separate queries , build 1 variable in programming language (like php, python.. etc.)

select      d.id doc_id,     count(v.document_id) num_upvotes votes v join document d on d.id = v.document_id v.upvoted true group doc_id ); 

that return list of upvoted documents. can same downvotes.

then after select document loop compare votes document id , build dictionary or list.

---

the second way can take lot longer @ runtime if have bunch of records in table (its less efficient, easier write) add subquery selects in select statement this...

select      logical_name ,     document.id ,      file_type ,      physical_name ,      uploader_notes ,      views ,      downloads ,      user.name ,      category.name category_name,     (select count(1) votes upvoted=true , document_id=document.id )as upvoted,     (select count(1) votes upvoted=false , document_id=document.id) downvoted  document  inner join category on document.category_id = category.id  inner join user on document.uploader_id = user.id  order category.id