rwarbelow/the_swot_bot — pull request #265 by mikelikesbikes (2013-10-29 14:40:36+00:00)
Title: add missing foreign key indexes

When these tables were created the foreign key indexes were overlooked.
Unindexed foreign keys are the biggest performance killers in DB driven
apps. As joins are necessary to answer important questions for the
front-end, the DB has to figure out where to find the data for a join.
It will try to use indexes if they are present, but defaults to full
table scans if they aren't. Table scans are super super expensive, and
can totally be avoided by careful indexing.

I also created a spec, which when run, will verify that all tables
columns that end in "_id" or "_type" are indexed. This is only a smoke
test, and isn't a replacement for thinking about how the data will be
joined and queried. More indexes (e.g. composite foreign key indexes)
may still be necessary in the case of join table, but that it difficult
to detect programmatically.