Creating a separate table for statistical counter in mysql?

I have a table for posts as ID,开发者_如何学Python Title, Content, etc. I just added a column as counter. It is a simple counter of visits, as every time it will be updated as $i+1. In this method, I update the table on every visit, just after reading the row (in a single mysql session).

Is it a good idea to separate the counter as another table connected with the post table with ID as foreign key? In this method, updating a lighter table is faster, but every time I need to read two tables (showing the post and its statistics).

So, my answer is it depends on what you are trying to do. I'm going to give you some behind the scene info on MySQL so you can decide when it makes sense to and not to create a counter table.

MySQL has different table engines. When you create a new table, you specify which engine to use, the more common ones are MyISAM and Innodb. MyISAM is really good and fast at doing selects and Innodb is really fast at doing inserts. There are other differences between both but it's good to understand that the engine you select is important.

Based on the above, if you have a table that is usually read and has a ton of rows, it might make sense to keep that table as MyISAM and create a separate counter table using Innodb that that keeps getting updated. If you are implementing cache, this is another reason which this model would work better since you won't clear your cache for your table data every time you update the counter since you would be updating the counter on a different table.

Now, some may argue that you should using Innodb because it has many more benefits but there are replication strategies that would allow you to make the best of both worlds.

I hope this gives you a general understanding so you can then dig deeper and find your answer. More info at: http://www.mysqlperformanceblog.com/2007/07/01/implementing-efficient-counters-with-mysql/