spree/spree — pull request #4909 by BMorearty (2014-07-03 23:19:44+00:00)
Title: Add missing indexes.

Add a bunch of indexes that seem to be missing.

When reviewing, don't accept this blindly. Please take a look and see if what I did makes sense. My approach was:

* Read through schema.rb, focusing on columns ending in _id (but also some with _type).
* If A belongs_to B and it would be likely to start from B and look up A (or if I found some code that starts from B and looks up A), make sure the index exists. E.g., there are lots of references to `order.adjustments` in the code, so I added an index on `order_id` in the `spree_adjustments` table.
* If A belongs_to B and it would be *unlikely* to start from B and look up A, don't bother adding an index. E.g., you're not likely to have a Spree::Address and look up its associated Spree::Order.
* When in doubt, sometimes I chose to add the index and sometimes I didn't. Depending on how much doubt I had. :grinning:

I removed a few indexes as well, and commented the reasons for removal.

Deciding which columns to index is hard. People usually either forget to index an abc_id column when they create it, or they index the abc_id column upon creation. In either case, that decision is made without benchmarking because the column has just been created, so you have to go with an educated guess. In my case I could have used benchmarks because these are existing columns. I didn't, though, because every Spree store is different and because I found so many unindexed columns that it didn't seem practical. I couldn't just use specs speed as a proxy because the specs don't deal with large amounts of data that would be influenced by indexes.

So I took my best stab at it.