Looking for the best way to handle using mySQL/PHP for analytics and thus having multiple connections trying to update the same counters in the same row at the same time.
The problem being if I have a row like 'year-month-day-hour','hits' and I want to increase hits for every pageview, that's going to be a lot of connections trying to update that same field in the same row. I'm worried about contention and/or writing the wrong values.
Also this will be packaged for many clients on different kinds of hosting. Thanks for any suggestions.
Best Answer
You may need to create the counter table with 250 slots to spread the row locks.
First, here is the table:
All you need to do is insert into the random slots like this:
If you want to collect the counts, run this:
If you want to collapse the slots into a single number, do this once a week:
To reclaim all the disk space for this table, do this once a month:
Give it a Try !!!
To give credit where is credit is due, I got the answer from page 140 of the Book
I have the book and refer to it well.