<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>

CC BY-SA 4.0
2019-05-16T20:11:09.153Z
How many other databases are on the MySQL server that you're using?
Brad Bell
CC BY-SA 4.0
2019-05-17T22:52:52.517Z
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.
Shad
CC BY-SA 4.0
2019-05-20T15:01:53.393Z
Sounds suspiciously like https://github.com/craftcms/cms/issues/4037
Brad Bell
CC BY-SA 4.0
2019-05-22T15:02:40.503Z
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.
Shad
<craft3><mysql><query><optimization>
Database Query in debugger consistently showing over 2000ms
# 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. 

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 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.  
> **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

