Sunday, April 06, 2008

Two basic indexing tips ...

Here are two basic tips for proper indexing ...

Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?

Put indexes where the database can really use them, if a table is to be fully scanned anyway, it's indexes are unlikely to be used, unless you can compare those index entries with other indexes on tables that won't be fully scanned. Ordering is another game ;). See here for an example.