I have 35 large (1M plus rows with 35 columns) databases and each one gets updated with per row imports based on the primary key.
I am thinking about grouping these updates into blocks, disabling the keys and then re-enabling them.
Does anyone know when disabling the keys is recommended. i.e. If I was going to update a single record it'd be a terrible idea but if I wanted to update every record, it would be a good idea. Are there any mathematical formulae to follow for this or should I just keep benchmarking?
I would disable my keys when I notice that there are particular performance effects on inserts / updates. These are the most prone to getting bogged down in foreign-key problems. Inserting a row into a fully keyed/indexed table with tens of millions of records can be a nightmare, if there are alot of columns and non-null attributes in the insert. I wouldnt worry about keys/indices in a small table --- in smaller tables (lets say ~500,000 rows or less with maybe 6 or 7 columns) the keys probably aren't going to kill you.
As hinted above, you must also consider disabling the real-time management of indices when you are doing this. Indices, if maintained by the database in real-time, will slow down operations that change the tables in the database as well.
Regarding mathematical forumlae : You can look at the trends in your insert/update speed when you do / do not have indices, with respect to database size. At some point (i.e. one your db reaches a certain size) you might find that the time for an insert starts increasing geometrically .... Or that it takes a steep "jump". If you can find these points in your system, you'll know when you are pushing it to the limit --- and a good admin might even be able to tell you WHY , at those points, the system performance is dropping.
Ironically -- sometimes keys/indices speed things up ! Indices and keys can speed up some updates and inserts by making any subqueries or other operations EXTREMELY (linear-time) fast. So if an operation is slow you might ask yourself "Is there some static data that i can index to speed lookup operation up " ?