Potential missing database index

Hi guys,

We have been using the salesforce suite with great success for a number of months now, and have made good use of it's in-built functionality. We've had some performance issues on our site though, and after a couple of days of investigation we have discovered a big performance issue in the Salesforce suite; which was very easily rectified.

In function salesforce_api_id_load (circa salesforce_api.module:1229) you use a simple select to get the SFID for a Drupal object. Looks like this:

SELECT sfid, name FROM {salesforce_object_map} WHERE drupal_entity = :drupal_entity AND oid = :oid

Amazingly, in a large scale system, this query is surprisingly slow, because there isn't an appropriate database index for it.

For some maths: we have around 100,000 users on our site, each mapped to SFDC, and along with other content, about 200,000 rows in all in the salesforce_object_map. MySQL used the entitiy_bundle_oid as the primary index for this query, which, with the inclusion of "drupal_bundle" in it, often returned around 5,000 rows to be re-searched (with no index) to return the relevant user. On our server (a powerful machine) this took about 0.30 seconds. Not terribly shabby.. but on some pages of our site, we have lists of 30 users, and they were taking up to 20 seconds to load, because this query was shooting into the processlist 30 times, and 30 * 0.30 = 9 seconds, minimum.

We ran a few tests over our setup, and discovered that literally, the addition of an index on oid improved performance not only on these user-list pages (the worst culprits) but across the site on the whole. Here's the mysql command for anyone reading this:

create index patch_oid on salesforce_object_map (oid);

On our test server (over 200,000 rows, but only 2Gb RAM and single core 2GHz CPU) it took about 22 seconds to build the index. On our live server (a beast with over 8Gb RAM) it took less than one!! There were no apparent negative effects on innodb or ibdata, and indeed, has resulted in a clear drop in long term server load average.

The select queries that used to take 0.30 seconds now immediately use the oid index, and report execution time of 0.0 - which is what one would expect. So, unless anyone can advise otherwise I highly recommend the addition of this index to the module :)