Hi Sorry for this somewhat long email but I think it is relevant to most people who run online databases. I am having trouble optimizing UPDATE queries on a certain semi-large table that is only growing larger. I've come across some very interesting thoughts from this list, so I thought I'll post my conundrum here. Would truly appreciate any guidance or pointers. I have done the messy DBA's job of breaking one table into three, which should actually have been one. Just did it to make sure the large main table doesn't keep getting updated with small crumbs of information. This main table stores what users of our online service have created. These are small Flash games, but over the last seven years, we have collected about 5 million of these. The primary key of this table is "GAME_ID". In our online account management interface I also need to show each producer's creations only to himself, so of course I need a "PRODUCER_ID". Above that, in our service we have had three more bits of information for each created game: - Number of views for the game (online views) - Number of plays of the game (online people playing) - Number of unique plays of the game (based on ip or subscriber_id..some games do not require login to play) Because each of these pieces of information was individual to each game, my original table looked like this: GAME Table ----------- GAME_ID PRODUCER_ID VIEWS_COUNT PLAYED_COUNT PLAYED_COUNT_UNIQUE In the beginning, everytime someone played a game online, we updated the PLAYED_COUNT in this table. When someone viewed a game, we updated the VIEWS_COUNT. It was simple and it worked as it reflected the business logic. Over time, however, I realized that while GAME_ID and PRODUCER_ID remained basically static, the next three columns had a very high rate of change. For example, VIEWS_COUNT would increment every second for popular games. The PLAYED_COUNT would increment everytime someone played, but the fact that we have "unique" into the equation means that I had to break this away into a log table, so that I could GROUP BY ip address and then update the columns accordingly in the GAME table. So I had: GAME_PLAYED_LOG Table ---------------------- GAME_ID PLAYER_IP_ADDR
