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