yii2: Super slow schema queries with MySQL 8.0.16

After updating to MySQL 8, I have noticed how super slow Yii2 has become. After using the Yii Debugger, I have found the following 6 queries consuming more than 95% of total loading time:

The only way I was able to fix the issue is by adding 'enableSchemaCache' => true, to the database component and enabling caching class' => 'yii\caching\FileCache', But since I’m using my machine for development I don’t want to enable caching.

Note: I tried the same code with MySQL version 5 and it was working fine. Seems an issue with MySQL 8 and Yii2, I’m not sure.

Additional info

Q	A
Yii version	2.0.22
PHP version	7.2.20
Operating system	macOS Mojave 10.14.5

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 44 (30 by maintainers)

Commits related to this issue

Super slow schema queries with MySQL 8.0.16 fix #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
changelog fix #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
fix #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
Fixing various schema drivers #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
Fixing various schema drivers #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
simple `$stop` #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
refactoring and add more test+validate #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
$resultIp move #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago
more test, fix code, refactoring, renaming #17444 — committed to kamarton/yiisoft-yii2 by kamarton 5 years ago

Most upvoted comments

Same problem.

alaa007 on Sep 9, 2019

My DB has 43 tables.

I tried the following code as you suggested through a direct script and it took a lot of time 0.30296200

$db = new PDO('mysql:host=localhost;dbname=ans', 'root', '');

$db->query('set profiling=1');
$db->query("SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
    (
        kcu.constraint_catalog = rc.constraint_catalog OR
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
    ) AND
    kcu.constraint_schema = rc.constraint_schema AND
    kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = 'yii_configuration' AND kcu.table_name = 'yii_configuration'");
$res = $db->query('show profiles');
$records = $res->fetchAll(PDO::FETCH_ASSOC);
$duration = $records[0]['Duration'];
echo $duration;

alaa007 on Jul 25, 2019

Doctrine’s affected by the same issue: https://github.com/doctrine/dbal/issues/4015

Slamdunk on May 20, 2020

Could you show the result of this query?

SHOW VARIABLES WHERE Variable_name LIKE 'information_schema_stats_expiry'

Not sure if it could make a difference but MySQL 8 caches schema stats by default, not sure if that would happen with an upgrade.

alex-code on Jul 25, 2019