counter optimize need help

mysql relatived

[Xuefer]

on mysql document, it is said:
1. QueryCache will be flushed when those tables is updated
2. separate columns into another table will increase disk seeks (if these rows not cached in memory)

but i have clickcount on each article, which updated frequently
any ideas?

[coopster]

Not sure what you are asking here, Xuefer. Are you considering ALTER table to move the

clickcount

column to a separate table in order to enhance the performance when executing queries against the

article

table? If so, and you intend to show and update the

clickcount

information every time somebody views the article, then the MySQL Query Cache [mysql.com] doesn't seem to be any type of alternative here in my own humble opinion.

This could grow to be an interesting thead...anybody differ in opinion or have alternative suggestions?

[Xuefer]

i can query clickcount and other data separately
then merge it by php script

there're many time the "article" table is locked too slow.
and many clickcount update is locked too
if clickcount is in separate table, it can be updated on the fly, because "select .. from .. where id in(...)" is very fast and won't lock too long

i was just asking how to improve frequently updated field