The query used by commerce_customer_profile_load() that becomes very inefficient once the row count in commerce_customer_profile gets higher. With the numbers I'm using - 350k and more - the query takes at least 3 seconds, and sometimes 5 seconds, to complete.

I fixed this by added a compound index on the type and uid fields - the following snippet works with MySQL and PostgreSQL.

CREATE INDEX idx_type_uid ON commerce_customer_profile (type, uid);

This reduced the query time to the millisecond range, and significantly sped up the work I was doing (mass user/profile import).

Comments

rszrama’s picture

rszrama commented
Issue tags: +Performance

Thanks for the tip; we hope to make 1.2 a performance release, so we'll definitely review this for it!

rszrama’s picture

rszrama commented
Status: Active » Fixed

Ok, I added your combined index but I also added individual index on the uid and type columns. It's possible those separate indexes would've been enough for you, but I see no harm in the combined index, too - especially since the Addressbook module will be depending on these columns for its queries.

Commit: http://drupalcode.org/project/commerce.git/commitdiff/6659876

rfay’s picture

rfay
he,him
Primary language English
Location Palisade, CO, USA
commented
Title: Add index to the commerce_customer_profile table » [TESTS BROKEN] Add index to the commerce_customer_profile table
Status: Fixed » Needs work

Not sure what happened here, but after this commit it's impossible to enable (any) commerce modules.

The problem is

      'uid_by_type' => array('uid', 'customer_profile_type'),

where there is no field "customer_profile_type". Maybe it was supposed to be "type"?

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'customer_profile_type' doesn't exist in table: CREATE TABLE {commerce_customer_profile} ( `profile_id` INT unsigned NOT NULL auto_increment COMMENT 'The primary identifier for a customer profile.', `revision_id` INT unsigned NOT NULL DEFAULT 0 COMMENT 'The current commerce_customer_profile_revision.revision_id version identifier.', `type` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'The commerce_customer_profile_type.type of this profile.', `uid` INT NOT NULL DEFAULT 0 COMMENT 'The users.uid that this profile belongs to.', `status` INT NOT NULL DEFAULT 0 COMMENT 'Boolean indicating whether the profile is active or not.', `created` INT NOT NULL DEFAULT 0 COMMENT 'The Unix timestamp when the profile was created.', `changed` INT NOT NULL DEFAULT 0 COMMENT 'The Unix timestamp when the profile was most recently saved.', `data` LONGBLOB NULL DEFAULT NULL COMMENT 'A serialized array of additional data.', PRIMARY KEY (`profile_id`), UNIQUE KEY `revision_id` (`revision_id`), INDEX `uid` (`uid`), INDEX `customer_profile_type` (`type`), INDEX `uid_by_type` (`uid`, `customer_profile_type`) ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'The base table for customer profiles.'; Array ( )

rfay’s picture

rfay
he,him
Primary language English
Location Palisade, CO, USA
commented
Priority: Normal » Critical

Actually critical. It's not just the tests that are broken, of course. Commerce can't currently be installed.

rszrama’s picture

rszrama commented
Title: [TESTS BROKEN] Add index to the commerce_customer_profile table » Add index to the commerce_customer_profile table
Priority: Critical » Normal
Status: Needs work » Fixed

Yeah, it was a stupid copy / paste mistake from the line above, and obviously I only tested the update process and not a clean install with the new index. D'oh. :-/

Committed the fix and reset the issue. Ran through a complete install to ensure none of the other index additions had errant column names. Looks like this was the only one. (In case you run into it in some other project, we can't use just "type" as the name of an index, but prepend the entity name to it to avoid reserved keyword problems or something w/ SQLite. Obviously we should use the column name when setting up the index; I had just copied the wrong value from the line before or something similarly braindead.)

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