Count efficiency vs storing a total

If using Innodb tables with properly optimized indexes and foreign keys, two of my tables are `user` and photo.

(for example)

In `user` I have - id, username, photoCount
Then in `photo` I have - id, fileContents, userId

Everytime a photo is inserted into `photo` a query is also run the `user` table to do photoCount = photoCount+1

Does it make more sense to keep it like this and just do:

SELECT `user`.username, `photo`.photoCount 
FROM `user` 
INNER JOIN `picture` ON `user`.id = `picture`.`userId` 
WHERE `user`.id='1' 

Code (markup):

OR remove photoCount and just do it dynamically with a count query?

SELECT `user`.username, COUNT(`picture`.id) 
FROM `user` 
INNER JOIN `picture` ON `user`.id = `picture`.`userId` 
WHERE `user`.id='1' 

Code (markup):

 

It depends. How often are you retrieving the count of photos? How large are you tables?

Theoretically the correct way is to do it dynamically with a count query, however in the real world there are exceptions and this may just be one of those times.

 

If you have large data best part is saving count in your use table if you don't have that large data use count statement

 

Saving the photo count is the way to go. It is really fast when your database grows.

The correct query will be (modified your first query)

SELECT username, photoCount  FROM `user` WHERE id='1'

Code (markup):

Remember to do photoCount-1 when a use deletes a picture.

 

i agree, saving the count is the way to go, however...

i have a medium sized social network where i used a little trick. i stored the users photos on the server in the /photos/USERID/ and when i needed the count I just counted the files in that directory. fast and you don't have to store any metadata