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
ActiveRecord Performance | 169
One complication in indexing decisions is that certain DBMSs (MySQL 4 and earlier,
and PostgreSQL 8.0 and earlier) will use at most a single index on each table per
query. This means that you should choose your indexes carefully. Consider this
query, which shows a list of a user??™s payments, showing the most recent first:
SELECT * FROM payments WHERE user_id = 12345 ORDER BY paid_at DESC;
Even if there are separate indexes defined on (user_id) and (paid_at), the best those
DBMSs can do is to use the user_id index and manually sort on paid_at, without the
help of an index. In order to use an index, you must define one that includes both
columns: (user_id, paid_at). This situation happens often when using acts_as_list:
many times you want to filter based on a foreign key and then sort by a position
column.
Check your DBMS manual for details. Some database systems have further restrictions
or hints. For example, on multicolumn indexes, PostgreSQL performs best
when the most restrictive condition applies to the leftmost column in the index.
Pages:
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262