Create or Update Row

[David Cook]

Hello,

I'm trying to figure out whats the best way to update a row or if that row doesn't exist then create a new row.

You see I'm working with MySQL and have a large sum of data that I'd like to aggregate for each day. So a user votes on an item and then their vote is stored for that day. I need to track the votes for each user and the votes for each item. The problem is that I have several thousand votes a day and then I display graphs for the votes across time periods (day, week, month, year, etc.). So in order to put a little relief on the DB I'd like to aggregate these votes into a row for each day. So if I'm dealing with a years worth of votes intead of querying thousands of individual user votes I'm querying a couple hundred aggregated rows.

My problem is that with the large amount of votes I'm concerned about mutual exclusion, that two users may vote at the same time and create multiple rows for the same day. Is this something I need to worry about? If so how should I address the issue?

Right now I'm just using PHP to run a couple queries like this:

SELECT * FROM aggregate WHERE item_id = "[item number]" AND date_created = "[date]"

if (rows >= 1)
{ update the aggregated row for that day }

else
{ create a new row for the aggregated day }

So my worry would just be that multiple people might swoop in at the same time and create multiple rows for the same day. Do I need to worry about this, it seems like something a transaction would be good for but I'm just not sure. Also if I do need to use transactions will this slow the process down at all?

Since I have to create an aggregate row for every day it seems I would have to run this check every single time a vote for an item is cast. Does this sound right or is there a better alternative?

Any help would be greatly appreciated.

Thanks.