Resolving unexpected slow database queries after MySQL 5.7 upgrade

MySQL 5.7 introduced the optimization setting derived_merge, with it being set to on by default.  While this setting is in most cases an effective optimization, it can cause problems for some complex custom database queries involving multiple sub-queries (or JOIN queries). To test if disabling the setting will improve the response of a specific slow query, do the following. 

Note

For consistency in results, you may want to temporarily disable the MySQL query cache while testing.

Run the query and check the response time.  Then disable the optimization setting by running the following against your database:

SET SESSION optimizer_switch='derived_merge=off';

Then run the query again and check the response time.  If you find a significant improvement in the query response time, you can then use the following override code to disable the derived_merge setting for your site on a 'per session' basis.

You can add the code in your settings.php file for Cloud Platform users, or in your pre-settings-php and post-settings-php hooks for Site Factory users.

Note

The following derived_merge override code is specific to MySQL 5.7.

Cloud Platform users

Drupal 7:

if (file_exists('/var/www/site-php')) {
  $conf['acquia_hosting_settings_autoconnect'] = FALSE;
  // EDIT next line to proper path to include file.
  require('/var/www/site-php/XXXXXX/XXXXX-settings.inc');
  $databases['default']['default']['init_commands'] = array(
    'optimizer_switch' => "SET SESSION optimizer_switch='derived_merge=off'",
  );
 acquia_hosting_db_choose_active();
}

Drupal 8:

if (file_exists('/var/www/site-php')) {
  global $conf, $databases;
  $conf['acquia_hosting_settings_autoconnect'] = FALSE;
  // EDIT next line to proper path to include file.
  require('/var/www/site-php/XXXXXX/XXXXX-settings.inc');
  $databases['default']['default']['init_commands'] = array(
    'optimizer_switch' => "SET SESSION optimizer_switch='derived_merge=off'",
  );
  acquia_hosting_db_choose_active();
}

Site Factory users

For Site Factory users, the above snippet needs to be split in two. The first snippet should be placed in your pre-settings-php hook, and the latter in your post-settings-php hook:

pre-settings-php

Drupal 7:

$conf['acquia_hosting_settings_autoconnect'] = FALSE;

Drupal 8:

global $conf, $databases;
$conf['acquia_hosting_settings_autoconnect'] = FALSE;

post-settings-php

Drupal 7 and Drupal 8:

$databases['default']['default']['init_commands'] = array(
  'optimizer_switch' => "SET SESSION optimizer_switch='derived_merge=off'",
);
acquia_hosting_db_choose_active();