4th December, 2007

Indexing isn’t hard

Filed under: database — admin @ 7:45 am

I found this blog post via Reddit and was happy to see the positive benefits the author is getting from profiling his SQL and indexing his database. He could have avoided the slow running query in the first place by following Andy’s simple rules of indexing;

  1. If your database doesn’t do it automatically, add indexes to all of your primary key columns
  2. Add indexes to all of your natural key columns
  3. Add indexes to all of your foreign key columns

When primary or foreign keys are composites (i.e. contain more than one column) create an index containing all of the columns in the same order that they are defined in the corresponding constraint.

If none of this makes any sense and you are responsible for your application’s database give me a call, my consulting rates are very reasonable.

Powered by WordPress