Friday, May 20, 2011

Some simple schema design mistakes

1. Not paying attention when determining the primary key, particularly if using index-organized primary storage structures like InnoDB or Oracle's "ORGANIZATION INDEX" tables.

2. Thinking that defining an index on multiple columns means that each column is "indexed". Multi-column indexes give you a search structure that is organized from the leftmost column to the rightmost column in the CREATE INDEX (or KEY()) statement, so if the leftmost column(s) aren't used in a given query, columns in the "middle" can't be used.

No comments: