Database Optimization techniques for amateurs
rdbms-agnostic, sql-optimization
Solution
Learn about indexes, and use them properly. Generally speaking*, follow these guidelines:
- Every table should have a clustered index
- Fields used for filters and sorts are good candidates for indexing
- More selective fields are better candidates for indexing
- For best performance on crucial queries, design "covering indexes" for those queries
- Make sure your indexes are actually being used, and remove those that aren't
- If your table has 15 fields, and you make 15 indexes, each with only a single field, you're doing it wrong :)
*There are some exceptions to these rules if you know what you're doing. My experience is Microsoft SQL Server, but I would presume most of this advice would still apply to a different RDMS.
Problem
Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being too vague, let's say we are using a maintstream DB such as MySQL or Oracle, and that the DB will contain 500,000-1m or so records across ~10 tables, some with foreign key contraints, all using the most typical storage engines (eg: InnoDB for MySQL). And of course, the basics such as PKs are defined as well as FK contraints.