Body: <p>In trying to optimize my Craft 3 site, I have discovered that it's apparently taking longer than expected to query my database. The duration for the apparent culprit is typically between 1900 and 2300ms alone. </p>
  
  <p><a href="https://i.sstatic.net/LRsYy.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/LRsYy.png" alt="enter image description here"></a></p>
  
  <p>The query is as follows:</p>
  
  <pre><code>SELECT
  `kcu`.`CONSTRAINT_NAME` AS `name`,
  `kcu`.`COLUMN_NAME` AS `column_name`,
  `tc`.`CONSTRAINT_TYPE` AS `type`,
  CASE
      WHEN NULL IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = DATABASE() THEN NULL
      ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
  END AS `foreign_table_schema`,
  `kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
  `kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
  `rc`.`UPDATE_RULE` AS `on_update`,
  `rc`.`DELETE_RULE` AS `on_delete`,
  `kcu`.`ORDINAL_POSITION` AS `position'
  FROM
  `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
  `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`,
  `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
  WHERE
  `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'resourcepaths'
  AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`TABLE_NAME` = 'resourcepaths' AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
  AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'resourcepaths' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` = 'FOREIGN KEY'
  UNION
  SELECT
  `kcu`.`CONSTRAINT_NAME` AS `name`,
  `kcu`.`COLUMN_NAME` AS `column_name`,
  `tc`.`CONSTRAINT_TYPE` AS `type`,
  NULL AS `foreign_table_schema`,
  NULL AS `foreign_table_name`,
  NULL AS `foreign_column_name`,
  NULL AS `on_update`,
  NULL AS `on_delete`,
  `kcu`.`ORDINAL_POSITION` AS `position`
  FROM
  `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
  `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
  WHERE
  `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`TABLE_NAME` = 'resourcepaths'
  AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'resourcepaths' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` IN ('PRIMARY KEY', 'UNIQUE')
  ORDER BY `position` ASC
  </code></pre>
  
  <p>I'm not having much luck in figuring out how to fix this, and as I understand it, there's not a lot that can be done on Craft's end, but I'm at a loss as to where to go next. I've moved over to a VPN (Dreamhost) and I've added the following to my config/app.php:</p>
  
  <pre><code>return [
  'components' =&gt; [
      'db' =&gt; function() {
          $config = craft\helpers\App::dbConfig();
          $config['schemaCacheDuration'] = 60 * 60 * 24; // 1 day
          return Craft::createObject($config);
      },
    ],
  ];
  </code></pre>
  
  <p>Any thoughts on where to go from here? Any ideas would be greatly appreciated.</p>
  
  <p>Thanks.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-05-16T20:11:09.153Z
Comments.Text: How many other databases are on the MySQL server that you're using?
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-05-17T22:52:52.517Z
Comments.Text: Hi Brad, thanks for responding. I have 10 other databases, but I know for a fact that three of them are attached to sites that are pretty much dead.
Comments.UserDisplayName: Shad
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-05-20T15:01:53.393Z
Comments.Text: Sounds suspiciously like https://github.com/craftcms/cms/issues/4037
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-05-22T15:02:40.503Z
Comments.Text: Yeah, I had seen that. Added the bit of code Brandon had suggested. Moved to a VPS too. Beginning to think my host (Dreamhost) might be doing something weird. I just don't know. Thanks again for the response.
Comments.UserDisplayName: Shad
Tags: <craft3><mysql><query><optimization>
Title: Database Query in debugger consistently showing over 2000ms
ThreadText: # Database Query in debugger consistently showing over 2000ms
  
  (Asked by: username_0 on 2019-05-16)
  
  In trying to optimize my Craft 3 site, I have discovered that it's apparently taking longer than expected to query my database. The duration for the apparent culprit is typically between 1900 and 2300ms alone.
  
  [![enter image description here](https://i.sstatic.net/LRsYy.png)](https://i.sstatic.net/LRsYy.png)
  
  The query is as follows:
  
  ```
  SELECT
  `kcu`.`CONSTRAINT_NAME` AS `name`,
  `kcu`.`COLUMN_NAME` AS `column_name`,
  `tc`.`CONSTRAINT_TYPE` AS `type`,
  CASE
      WHEN NULL IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = DATABASE() THEN NULL
      ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
  END AS `foreign_table_schema`,
  `kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
  `kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
  `rc`.`UPDATE_RULE` AS `on_update`,
  `rc`.`DELETE_RULE` AS `on_delete`,
  `kcu`.`ORDINAL_POSITION` AS `position'
  FROM
  `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
  `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`,
  `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
  WHERE
  `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'resourcepaths'
  AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`TABLE_NAME` = 'resourcepaths' AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
  AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'resourcepaths' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` = 'FOREIGN KEY'
  UNION
  SELECT
  `kcu`.`CONSTRAINT_NAME` AS `name`,
  `kcu`.`COLUMN_NAME` AS `column_name`,
  `tc`.`CONSTRAINT_TYPE` AS `type`,
  NULL AS `foreign_table_schema`,
  NULL AS `foreign_table_name`,
  NULL AS `foreign_column_name`,
  NULL AS `on_update`,
  NULL AS `on_delete`,
  `kcu`.`ORDINAL_POSITION` AS `position`
  FROM
  `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
  `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
  WHERE
  `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, DATABASE()) AND `kcu`.`TABLE_NAME` = 'resourcepaths'
  AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = 'resourcepaths' AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` IN ('PRIMARY KEY', 'UNIQUE')
  ORDER BY `position` ASC
  
  ```
  
  I'm not having much luck in figuring out how to fix this, and as I understand it, there's not a lot that can be done on Craft's end, but I'm at a loss as to where to go next. I've moved over to a VPN (Dreamhost) and I've added the following to my config/app.php:
  
  ```
  return [
  'components' => [
      'db' => function() {
          $config = craft\helpers\App::dbConfig();
          $config['schemaCacheDuration'] = 60 * 60 * 24; // 1 day
          return Craft::createObject($config);
      },
    ],
  ];
  
  ```
  
  Any thoughts on where to go from here? Any ideas would be greatly appreciated.
  
  Thanks.
  
  **Tags:** craft3, mysql, query, optimization
  
  > **Comments:**
  > **username_1**: How many other databases are on the MySQL server that you're using?  
  > **username_0**: Hi username_1, thanks for responding. I have 10 other databases, but I know for a fact that three of them are attached to sites that are pretty much dead.  
  > **username_1**: Sounds suspiciously like https://github.com/craftcms/cms/issues/4037  
  > **username_0**: Yeah, I had seen that. Added the bit of code Brandon had suggested. Moved to a VPS too. Beginning to think my host (Dreamhost) might be doing something weird. I just don't know. Thanks again for the response.  
  
  ---
  
  ## 0 answers
  
  