While running the 7101-upgrade on a 70k-sized database I noticed that there is no index on redhen_engagement.contact_id after creating that index the migration took only one second (as opposed to several minutes).
I had a look at the hook_schema implementation that define foreign keys - but even newly installed drupal sites missed those foreign keys (and therefore indexes on the affected columns). That's when I finally found: http://drupal.org/node/911352 .
In short: D7 simply ignores foreign keys defined in hook_schema(). All foreign keys need to be declared explicitly in hook_install() using ALTER TABLE.
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | redhen_engagement_foreign_keys.patch | 1.01 KB | torotil |
| #3 | redhen_foreign_key_indexes.patch | 7.22 KB | torotil |
| #2 | redhen_foreign_key_indexes.patch | 8.09 KB | torotil |
| #1 | redhen_foreign_key_indexes.patch | 8.09 KB | torotil |
Comments
Comment #1
torotil commentedHere's a patch that explicitly creates indexes for all sets of columns that are declared as foreign keys (if not already done) - including upgrade hooks.
Comment #2
torotil commentedSorry for double-posting - d.o just had a weird error.
Comment #3
torotil commentedThere were also some obsolete foreign-key entries in redhen_contact.install. I've fixed them too.
Comment #4
torotil commentedIt seems that my update hook for redhen_engagement got lost. Here is the patch.
Comment #5
levelos commentedThanks torotil, committed with minor variations!