One important feature Magento provides for improved performance is “Flat Products and Categories”. Magento stores data of single product in multiple tables. Like name is stored in catalog_product_entity_varchar table, id is stored in catalog_product_entity_int table, price is stored in catalog_product_entity_decimal table. Under this scheme, in order to fetch a product data must be fetched from these multiple tables. This cause queries to take much time as compared to having data in single table. Same goes for categories.
To overcome this disadvantage Magento can put all data in a separate single table and fetch it from there when needed. Using this can give significant improvement in performance for catalogs of 1000+ products.
Here is how you can enable this feature.
First build or reindex “Product Flat Data” index from
Admin Panel->System->Index Management and click “Reindex Data” against “Product Flat Data”. After building index go to Admin Panel -> System -> Configuration -> Frontend and select “Yes” against “Use Flat Catalog Product”. You only need to enable this option once, but you may need to rebuild index data periodically unless “Index Mode” is set to “Update on Save”.
Same is to be done to enable flat categories.
More on Magento Performance