function hosting_package_update_6006 in Hostmaster (Aegir) 6

#1033072 - Add indexes on package tables to improve performance with huge package sets

When hosting_package_instance hits > 150k rows, the code in hosting_package.instance.inc degrades really badly, yielding queries of 10-20 seconds and yielding to frontend requests of 1 to 5 minutes for the migrate dialog.

The indexes created here improves that performance notably, the queries going down to 0.1s and the requests down to < 10s.

See also

_hosting_package_temporary_table()

hosting_package_comparison()

File

modules/hosting/package/hosting_package.install, line 435

Install, update and uninstall for the package management module.

Code

function hosting_package_update_6006() {
  $ret = array();
  db_add_index($ret, 'hosting_package_instance', 'hosting_package_id_idx', array(
    'package_id',
  ));
  db_add_index($ret, 'hosting_package_instance', 'hosting_package_rid_idx', array(
    'rid',
  ));
  db_add_index($ret, 'hosting_package', 'hosting_package_nid_idx', array(
    'nid',
  ));
  return $ret;
}