otwcode/otwarchive — pull request #463 by ambtus (2012-02-03 21:24:41+00:00)
Title: making filter_tagging have a composite index

from james:
around 22% improvement can be made by making the primary key include filter_id,
additionally partitioning that table on filter_id helps the query by around 32%.
I would recommend letting the system bed down for a week or so before we do the partitioning

my thoughts:

As far as I can tell, the only problem with not having id be the primary key all by itself (which is what rails expects) is that it might not be unique, and rails is expecting it to be. Because the id field is auto_increment and the primary key is identified with id first, we won't end up with two FilterTagging records with the same id except with a direct sql insert.

If we did have duplicate FilterTagging ids, this would only be a problem if we were trying to access and manipulate individual FilterTagging records which were found by id. We don't currently do so. Just in case we decide to do so in the future, I've modified the find statements for FilterTagging to trigger an exception and a reminder to install the composite_primary_keys gem. I didn't install the gem because I think it adds too much complexity and is overkill for our situation. Besides, I couldn't get it to work :)