We've been asked to rework a billing system that copes with about 200.000 records per day. A billing record represents an incoming message that can be of a certain type. Currently our system handles about 200.000 messages per day, and this is all outputted in a single table. (time_sent, entity_id, type_id). At the end of the month, finance needs to draw reports for each entity_id how many messages have been handled during the month. It's difficult to adjust the system entirely, we'd rather not touch the one large billing table. However it is possible, so any suggestions are welcome. Also, we need to have the details of the time the message was sent. Now our idea was, to add a trigger to this table that starts writing to a counter table for every day (entity_id, type_id, year, month, day, count), which would obviously already speed up the reports significantly. However, since we have about 5000 entities, and 16 billing_types, this table would also grow out of proportions fairly fast. The number of entities, and number messages are expected to increase by 20-30% per year, and we obviously need to be able to work with this for a good number of years. Therefore we want to have a similar table, that contains the totals for each month. Our goal is to have a responsive front-end, even when we try to look in the detail table for a single entity_id on a certain date.. Obviously the table with the billing details is indexed on the time_sent and entity_id columns. Most of these messages come in chronologically, but this is not necessary. Now I have a number of questions, and all suggestions are more than welcome). a) Is this a good approach? And why/why not? b) Any better ideas? c) How certain can we be that these triggers (and recursive triggers, if we want to have a table for monthly stats) are executed? d) Any pitfalls we should watch out for? e) Besides the indexes on the billing detail table, is there another way to increase the performance on this table? Any advice is more than welcome and really appreciated.