Well, I've tried to do massive UPDATEs as much as possible. But the patterns that we're looking for are basically of the variety, "If the user clicks on X and then clicks on Y, but without Z between the two of them, and if these are all part of the same simulation run, then we tag action X as being of interest to us." So it's oodles of keeping track of back-and-forth for each of the rows in the table, and looking forward and backward in the table. I agree that row-at-a-time thinking isn't the best way to work, but I didn't see a good alternative for our purposes. I'm open to any and all suggestions.
Can you post at least some table schemas, indexes, queries, and explain output? I think of database optimization as a serious case of devil in the details, and generalities (like -- make sure you index, make sure your indexes help using explain, avoid row-at-a-time thinking) won't get you far. So if we had something concrete to work with, well, we would have something concrete to work with.
