We are currently operating a website that deals with over 20,000 items and I recently researched how to decrease page load times. One of the recurring suggestions I've seen was to enable "Use Flat Catalog Product/Category".
From my understanding, this combines the product tables into one so it's quicker to access but I was wondering if there are any negative side effects in enabling this option? Is there any potential that this could conflict with the live site and/or make some pages unusable? My hope is to enable the option but I want to be sure it will create a positive impact.
The flat catalog will reduce the load on your MySQL server by combining all the product/category tables into one big table instead of performing expensive table joins on every page load. Generally you will see greater benefit the more products/categories you have. One of the downsides is that each time you make a change to a product the flat catalog tables have to be re-indexed. There is also an edge case where the flat tables may exceed the MySQL row limit on stores with a large number of custom attributes, but this is hardly an issue for most users.
In most cases your store performance will benefit from the reduced query complexity so it is worth trying.
On a development site it is better to keep it turned off so that changes become visible immediately without having to do a re-index.