Sunday, April 06, 2008

Two basic indexing tips ...

Here are two basic tips for proper indexing ...

  1. 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?
  2. 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.
Easy, isn't it? But these mistakes are still very common ...

2 comments:

Arjen said...

MySQL does not currently support function-based indexes, unless you do it with an extra column (maintained with a trigger).

pabloj said...

Thanks arjen, I know that, (already blogged about function based indexes), didn't want to get too database specific.