Resolving unexpected slow database queries after MySQL 5.7 upgrade

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

Run the query and check the response time.  Then disable the optimisation 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.

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();