168 | Chapter 6: Performance
There is really no substitute for understanding what queries are being sent to the
database and how they are being satisfied.
Be sure to think critically about where you need indexes, as well as where you don’t
need them. In particular, you should omit indexes on tables that are very infre-
quently read from (such as audit logs). Gratuitous indexes can hurt write perfor-
mance, as they must be updated when data in the table is changed.
Foreign key indexes
Foreign keys are the most common place where indexes are needed (and often omit-
ted). In fact, MySQL generates an index on the referencing column automatically
when a foreign key is created.*PostgreSQL does not; you must create all indexes
except for those on primary keys manually. Foreign key indexes assist in queries by
associations, such as:
SELECT * FROM projects WHERE user_id = 123;Without a proper index onuser_id, every row in theprojectstable must be exam-
ined. It is standard practice to create an index on most foreign keys. However, there
are exceptions. Indexes do not help on attributes of low cardinality—those where
there are few unique values. The standard example is an index on thesexcolumn of
a person; there are (generally) only two possible values. For a clause such asWHERE
sex = 'M', an index lookup would probably take longer than a full table scan.
This concern also applies to lookup tables (type tables) that simply serve to define
values for an attribute. If the possible values are small or unevenly distributed, an
index might slow things down. An example would be a foreign key into an order sta-
tus lookup table, as shown in Table 6-1.
Other indexes
Foreign keys are just the beginning, however, and they are the easiest thing to get
right. The rest is highly application-specific, so you will have to look at the queries
you are actually issuing to get a feel for where you need indexes. Indexing can
quickly look more like an art than a science.
- Some would consider this behavior helpful; others consider it presumptuous, as indexes are not always
needed on foreign keys.
Table 6-1. order_status
status_id status
1 opened
2 billed
3 shipped
4 returned