I’m wondering if people have any tips for maintaining good perfomance as the records counts in the database grow. We are currently using 7.2.1
There are many things that make the history (as well as other) table slow. Here’s a few::
A – Over time, the history table tends to end up with a lot of foreign keys that are invalid. You need to NULL out all the invalid keys or point them to real records.
B – Missing indices on foreign key fields. EVERY foreign key field in every table should be tagged w/an index.
C – Not re-indexing the db on a regular basis. For large highly active db’s, this should be at least once a day (after hours of course).