Gratuitous indexes can hurt write performance,
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 omitted).
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 on user_id, every row in the projects table must be examined.
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 the sex column of
a person; there are (generally) only two possible values. For a clause such as WHERE
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 status
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.
Pages:
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261