MySQL Forums
Forum List  »  Performance

Total column or dynamic count
Posted by: Dan Thorpe
Date: July 28, 2011 07:28AM


I have an Innodb table with over 30,000 records and over 20 columns, I'm trying to cut down on unused data for optimization.

Basically a few of the columns are totals on user actions, so for example if a user uploads a picture then pictureCount would be pictureCount+1

However the pictures are also inserted into a pictures table.

My question is having the totals in a column a better practise than doing something like ..

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

At the moment there are update queries for the on action as well as queries to insert the pictures, the above would cut out the update queries but is a large count query more intensive?

Options: ReplyQuote

Written By
Total column or dynamic count
July 28, 2011 07:28AM
July 30, 2011 12:37AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.