1

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

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.

  • How many other databases are on the MySQL server that you're using? – Brad Bell May 16 '19 at 20:11
  • 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 May 17 '19 at 22:52
  • 1
    Sounds suspiciously like github.com/craftcms/cms/issues/4037 – Brad Bell May 20 '19 at 15:01
  • 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 May 22 '19 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.