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.

Comments

Status:Active» Needs review
StatusFileSize
new8.09 KB

Here'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.

StatusFileSize
new8.09 KB

Sorry for double-posting - d.o just had a weird error.

StatusFileSize
new7.22 KB

There were also some obsolete foreign-key entries in redhen_contact.install. I've fixed them too.

StatusFileSize
new1.01 KB

It seems that my update hook for redhen_engagement got lost. Here is the patch.

Status:Needs review» Fixed

Thanks torotil, committed with minor variations!

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.