I have two tables that are loaded with content-rich rows. One of the column's in the rows is pretty much a statistical counter, that keeps track of how many times that content has been viewed.
The tables are myisam right now, but I'm thinking of switching to innodb just because I'm having a major problem with delays due to these tables locking. I probably run about one-million (or more) +1 queries like so:
Doing one-million of those a day seems to be creating the issue I'm having with locking tables. Now, I have a few options I've been considering.
$addview = mysql_query("UPDATE table SET views=views+1 WHERE ... LIMIT 1",$rsc);
1. Just switch those two tables to innodb.
2. Create a new table on the server that is innodb and handles the UPDATE +1 queries.
3. Use an entirely new server dedicated to an innodb table to handle the update UPDATE +1 queries.
On both option 2 and option 3, I'd create an hourly PHP script that takes the numbers from the innodb table and update the content tables with the updated counts in a single query.
What sounds like the way to go? Option 1 is obviously easiest, but I heard in terms of speed/efficiency there are some drawbacks from going from myisam to innodb. I don't mind spending a bit more to get a server dedicated to keeping up with the UPDATE +1 queries.
All feedback appreciated.