---
title: "Resolving unexpected slow database queries after MySQL 5.7 upgrade"
date: "2025-02-05T23:49:18+00:00"
summary:
image:
type: "article"
url: "/acquia-cloud-platform/help/93171-resolving-unexpected-slow-database-queries-after-mysql-57-upgrade"
id: "8eb30be7-08ea-4ce0-818f-54ad0c888f6f"
---

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