Yii2: Become very slow after update from 2.0.5 to 2.0.14

[rosancoderian]

my app become very slow after update from 2.0.5 to 2.0.14.
it become normal after i downgrade it back to 2.0.5.

PHP version 7.1
Ubuntu 16.04
composer.json

{
    "name": "yiisoft/yii2-app-basic",
    "description": "Yii 2 Basic Project Template",
    "keywords": ["yii2", "framework", "basic", "project template"],
    "homepage": "http://www.yiiframework.com/",
    "type": "project",
    "license": "BSD-3-Clause",
    "support": {
        "issues": "https://github.com/yiisoft/yii2/issues?state=open",
        "forum": "http://www.yiiframework.com/forum/",
        "wiki": "http://www.yiiframework.com/wiki/",
        "irc": "irc://irc.freenode.net/yii",
        "source": "https://github.com/yiisoft/yii2"
    },
    "minimum-stability": "stable",
    "require": {
        "php": ">=5.4.0",
        "yiisoft/yii2": "2.0.14",
        "yiisoft/yii2-bootstrap": "~2.0.0",
        "yiisoft/yii2-swiftmailer": "~2.0.0",
        "moonlandsoft/yii2-phpexcel": "*",
        "mdmsoft/yii2-admin": "~2.0",
        "yiisoft/yii2-jui": "~2.0.0",
        "dompdf/dompdf": "^0.8.2"
    },
    "require-dev": {
        "yiisoft/yii2-debug": "~2.0.0",
        "yiisoft/yii2-gii": "~2.0.0",
        "yiisoft/yii2-faker": "~2.0.0",

        "codeception/base": "^2.2.3",
        "codeception/verify": "~0.3.1",
        "codeception/specify": "~0.4.3"
    },
    "config": {
        "process-timeout": 1800
    },
    "scripts": {
        "post-create-project-cmd": [
            "yii\\composer\\Installer::postCreateProject"
        ]
    },
    "extra": {
        "yii\\composer\\Installer::postCreateProject": {
            "setPermission": [
                {
                    "runtime": "0777",
                    "web/assets": "0777",
                    "yii": "0755"
                }
            ],
            "generateCookieValidationKey": [
                "config/web.php"
            ]
        },
        "asset-installer-paths": {
            "npm-asset-library": "vendor/npm",
            "bower-asset-library": "vendor/bower"
        }
    }
}

[lubosdz]

It is impossible to guess from your composer config what's the reason for slowing down :-)

I would recommend first deleting all caches (assets, cache, browser delete history) and if the issue still occurs then run profiler to narrow the reason - e.g. is it database, HTTP requests, javascript of PHP or what is actually slowing down .. Profiler will show you which files / classes / methods consume most of response time ..

[samdark]

Also please try 2.0.13.

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

[rosancoderian]

@samdark
i've tried the 2.0.13, it becomes faster than 2.0.14 but there's a weird behaviour, when i try to login, it request my login controller several times before it succesfully logged in, this was not happened when it still in version 2.0.5.

[samdark]

We've just tagged 2.0.14.1 btw. with regression fixes.

[samdark]

2.0.5 — 2.0.13 is still too broad. Maybe try git bisect to find out what change affected your project? http://en.rmcreative.ru/blog/finding-bug-with-binary-search/

[rosancoderian]

the problems occured only in version 2.0.13 and above

[samdark]

Still too broad. Please do git bisect to find out the culprit. Thanks.

[sergeymakinen]

And this behavior is probably related to a frontend. It doesn't seem to be related to a backend part of Yii.

[hooman-mirghasemi]

also when i update to 2.0.14.1 from 2.0.13.1 my project become a little slower

[SilverFire]

@hooman-pro we need some help to find a reason of a slowdown. Could you try git bisect to find out what change affected your project? http://en.rmcreative.ru/blog/finding-bug-with-binary-search/

[hooman-mirghasemi]

It is for sql runing before update to 2.014
in debug show 597 ms for db
and after it show 814 ms

[samdark]

Please do git bisect. It's impossible to find out what's wrong otherwise.

[hooman-mirghasemi]

I can not understand !
it don't related to my code because i check debug panel sql run time exactly before run composer update yiisoft/yii2
and exactly after it I check it again without changing any line of my code, also before and after updating yii there was 190 query.

It seems the frame work method for running sql changed

[samdark]

Yes but between the two versions you've mentioned there are many commits and you have a code to test against via git bisect. We don't have it.

[Eseperio]

Db Schema query, in my case, has became a lot slower after upgrading from 2.0.13.1 to 2.0.14.1. Up to 1 second. If schema cache is not enabled this can be the cause of slower websites.

Using MySQL
2.0.13.1 - 0.4ms

2.0.14.1 - 888ms

EDIT: Is not the same. it is a new method loadTableConstraints added in #14105

[Eseperio]

It is strange. I have two different projects updated to 2.0.14.1, but only one of them executes this query. Also this query does not display an origin in debug panel.

[samdark]

@Eseperio which DB server is that? MySQL?

[Eseperio]

Yes. MySQL MaríaDB. The problems comes when using DbSession.

[samdark]

@rosancoderian, @hooman-pro do you use DbSession?

@Eseperio that's because DBSession was switched to use atomic upserts to solve concurrency issues. Upsert requires extra schema data. In production it's cached so there should be no extra queries at all.

[hooman-mirghasemi]

Yes I use mysql too and store Session in Db

[Eseperio]

Stack trace from loadTableConstraints when using DbSession

#0  yii\db\mysql\Schema->loadTableConstraints(session, primaryKey) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/mysql/Schema.php:139]
--
  | #1  yii\db\mysql\Schema->loadTablePrimaryKey(session) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/Schema.php:744]
  | #2  yii\db\Schema->getTableMetadata({{%session}}, primaryKey, ) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/ConstraintFinderTrait.php:101]
  | #3  yii\db\mysql\Schema->getTablePrimaryKey({{%session}}) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/QueryBuilder.php:555]
  | #4  yii\db\QueryBuilder->getTableUniqueColumnNames({{%session}}, Array ([0] => `data`,[1] => `id`,[2] => `expire`), Array ()) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/QueryBuilder.php:527]
  | #5  yii\db\QueryBuilder->prepareUpsertColumns({{%session}}, Array ([data] => __flash\|a:0:{}__id\|i:2;__expire\|i:1519823468;lysenkobv\GeoIP\GeoIP:127.0.0.1\|O:22:"lysenkobv\GeoIP\Result":2:{s:7:"*data";N;s:13:"*attributes";a:1:{s:7:"isoCode";N;}},[id] => 25d5c53a45c31b5a80d2a18c7fc1cf9b,[expire] => 1519824308), 1) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/mysql/QueryBuilder.php:270]
  | #6  yii\db\mysql\QueryBuilder->upsert({{%session}}, Array ([data] => __flash\|a:0:{}__id\|i:2;__expire\|i:1519823468;lysenkobv\GeoIP\GeoIP:127.0.0.1\|O:22:"lysenkobv\GeoIP\Result":2:{s:7:"*data";N;s:13:"*attributes";a:1:{s:7:"isoCode";N;}},[id] => 25d5c53a45c31b5a80d2a18c7fc1cf9b,[expire] => 1519824308), 1, Array ([:qp0] => __flash\|a:0:{}__id\|i:2;__expire\|i:1519823468;lysenkobv\GeoIP\GeoIP:127.0.0.1\|O:22:"lysenkobv\GeoIP\Result":2:{s:7:"*data";N;s:13:"*attributes";a:1:{s:7:"isoCode";N;}},[:qp1] => 25d5c53a45c31b5a80d2a18c7fc1cf9b,[:qp2] => 1519824308)) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/db/Command.php:543]
  | #7  yii\db\Command->upsert({{%session}}, Array ([data] => __flash\|a:0:{}__id\|i:2;__expire\|i:1519823468;lysenkobv\GeoIP\GeoIP:127.0.0.1\|O:22:"lysenkobv\GeoIP\Result":2:{s:7:"*data";N;s:13:"*attributes";a:1:{s:7:"isoCode";N;}},[id] => 25d5c53a45c31b5a80d2a18c7fc1cf9b,[expire] => 1519824308)) called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/web/DbSession.php:174]
  | #8  yii\web\DbSession->writeSession(25d5c53a45c31b5a80d2a18c7fc1cf9b, __flash\|a:0:{}__id\|i:2;__expire\|i:1519823468;lysenkobv\GeoIP\GeoIP:127.0.0.1\|O:22:"lysenkobv\GeoIP\Result":2:{s:7:"*data";N;s:13:"*attributes";a:1:{s:7:"isoCode";N;}})
  | #9  session_write_close() called at [/Applications/XAMPP/xamppfiles/htdocs/pems/vendor/yiisoft/yii2/web/Session.php:192]
  | #10 yii\web\Session->close()

[Eseperio]

The change to use the new upsert method in 64d57043b1bfac062fa44715dc57593b678c4660 is the origin of the difference in load time

upsert method was introduced here de1750228d318f106a9f6f9054e0a9b3254fbd57

[Eseperio]

@samdark I did'nt see your edit. Ok. But it is a pain in the ass when developing locally.
It could be good to describe this change in performance on upgrade guide. Almost to remember users to enable schemacache.

[zhukovra]

@Eseperio what reason stops you to use schema cache when developing locally?

[Eseperio]

Updates in columns structure.

[zhukovra]

@Eseperio but applying migrations should flush schema cache, no?

[Eseperio]

Yes. Migrations are great, great if you develop a project in a team or if a project is modular. I use them everyday for projects with my team. But when developing simple project i don´t create migrations of any change i make, i use DDL. And IMHO i think this is what many user do.

[sergeymakinen]

Yii also provides a console command to flush cache (per cache/all registered caches).
In a local development it's about ctrl+r -> flush -> enter after you used it once.
But it should be noted in docs, I agree.

[SilverFire]

Added docs

[MartijnHols]

I suggest also mentioning it (in an obvious way) in any docs suggesting using DbSession, such as here: http://www.yiiframework.com/doc-2.0/yii-web-dbsession.html
Otherwise new users of this session data storage will run into this same issue. Also it was one of the first search results I ran into when I started debugging this issue. The new query adds about 2 seconds to every single request for me.

[SilverFire]

Wow, 2 seconds? O_o That's a really a lot of time.
I guess the query itself can be optimized. @MartijnHols could you help us to reproduce situation, when it takes 2 seconds to retrieve constraints?

[samdark]

Is that MySQL, @MartijnHols?

[MartijnHols]

MariaDB just like @Eseperio. On my work desktop it was about 1900ms (first call after restarting the service was 2500ms). I ran the query via HeidiSQL as well to see the results without hacking into the code and it took as much time, even after restarting the service. Using caching restored the response times so it didn't seem like a big deal to me,

Just now I tried reproducing it at my home pc and the same query only takes about 100ms, so there might be something else going on at my work computer. I'll try to find out if there's a difference in versions or settings or if I can explain it some other way tomorrow.

[MartijnHols]

Using the data in the information_schema database from my home pc that I exported it's as quick on my work computer. Because of this I guess it might be an issue with the contents of the information_schema database, my experience with joins is that they get slow when there's a lot of data being joined which led me to think it's either the amount of databases I have running (only 20 including the MySQL databases), or the fact I have multiple databases with session tables (about 5 of them I think).

I tried booting a new docker container of MariaDB and creating multiple databases (5) with the session table, but that didn't result in any noticeable slowdown. I tried exporting my work pc's information_schema and re-importing it in a different database (with tables made non-temporary because it wouldn't otherwise appear) and running the query on that is really quick.

I tried to find what part of the query is slow, and learned that each left-join is responsible for about half the execution time (900ms). Without the left joins it's near instant.
I tried changing the on-conditions so the records resulting from the left-joins is just for the current table (currently it fetches the constraints from all tables, not sure if intended), without any change.
Running queries directly on the respective tables is near instant, just the joining seems to be slow even though this:

SELECT #DISTINCT
    `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` = `sch`.`name` 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 (SELECT DATABASE() AS `name`) AS `sch`
INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
#LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
#    ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
#LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
#    ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
ORDER BY `kcu`.`ORDINAL_POSITION` ASC

takes ~16ms and results in only 1 record, so it's not like the left-joining should be any different from a single query directly on the table.

This takes 969ms:

SELECT #DISTINCT
    `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` = `sch`.`name` 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 (SELECT DATABASE() AS `name`) AS `sch`
INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `kcu`.`TABLE_SCHEMA` = COALESCE(NULL, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
#LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
#    ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
    ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
ORDER BY `kcu`.`ORDINAL_POSITION` ASC

This takes 78ms:

SELECT `tc`.`CONSTRAINT_TYPE` FROM `information_schema`.`TABLE_CONSTRAINTS` AS `tc` WHERE `tc`.`TABLE_SCHEMA` = 'my-database' AND `tc`.`CONSTRAINT_NAME` = 'PRIMARY'

I'm not sure how to continue debugging but in any case this looks to be an issue with MariaDB. It might be fixable by changing the query further but I'm not sure how to proceed with that as I'm not sure about everything it's used for. e.g. I can imagine the TABLE_CONSTRAINTS query could be a subquery instead which might be quick but that would result in only one record being returned (in the case of the session table that would be right, but the same query is likely also used for other tables).

I updated MariaDB to 10.2.13 without any changes.

Next I'll try to reproduce this in a Docker container and perhaps rebuild my database to see if it's maybe somehow corrupt. I'd be surprised if the issue remains afterwards, but if it does I'll start removing databases until the issues is fixed or just one Yii2-app-database remains. If it persists I'll strip that down to the bare minimum to reproduce the issue.

[beroso]

Hi @MartijnHols .
Could you try to benchmark the following query?
(replace :schema with your database name)

(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` = :schema 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` = :schema AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
     AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
     AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` 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`,
    CASE
        WHEN NULL IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = :schema 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`,
    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` = :schema AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
     AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
     AND `tc`.`CONSTRAINT_TYPE` in ('PRIMARY KEY', 'UNIQUE')
)
ORDER BY `position` ASC
;

[sergeymakinen]

I just checked my internal repo and realized that I put the first version of the query, not the optimized one. >_<
I'll make a PR tonight.

[MartijnHols]

@berosoboy Looks to be slower; 2750ms.

While doing the process of removing databases and tables not used by the Yii app I noticed it got quicker as I removed things with a single jump from 600ms to 100ms after reaching a certain threshold. My guess was that this had something to do with the query cache. This is kinda strange because I had seen earlier that the amount of records this query accesses was just 1*61 rows, but the jump from 600ms to 100ms clearly indicated swapping to disk paging after a certain threshold.

After some experimentation I found a possible solution in the MariaDB config. If I set table_open_cache to 750 or higher the first query takes 1100ms, but any queries afterwards are <100ms.

Running an explain on the query shows this:

This and the fact table_open_cache affects it suggests to me that maybe it opens all tables in the MariaDB server, paging to disk after reaching a certain amount? Probably a MariaDB bug.

My old setting for table_open_cache came from the my-large.ini default that I had freshly downloaded from the MariaDB download page;

table_open_cache = 256

[beroso]

@MartijnHols thanks for the reply.

Could you try again my proposed query, but now specifiying the
rc.TABLE_NAME = 'session' AND tc.TABLE_NAME = 'session' in the WHERE parts ?
I thik this change will prevent Scanning all databases

[MartijnHols]

(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` = :schema 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` = :schema AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
     AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
     AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
     AND `tc`.`CONSTRAINT_TYPE` = 'FOREIGN KEY'
     AND rc.TABLE_NAME = 'session' AND tc.TABLE_NAME = 'session'
)

UNION

(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` = :schema 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`,
    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` = :schema AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = 'session'
     AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
     AND `tc`.`CONSTRAINT_TYPE` in ('PRIMARY KEY', 'UNIQUE')
     AND tc.TABLE_NAME = 'session'
)
ORDER BY `position` ASC
;

is quick (~15ms) even without anything in the cache.

[beroso]

Awesome!

[sergeymakinen]

I had originally a query with 2 LEFT JOINs but when I replaced them with UNION… well I came to the @MartijnHols solution. :)
With some minor changes though.

[beroso]

@sergeymakinen did not you mean berosoboy? ;)

[samdark]

@berosoboy https://github.com/yiisoft/yii2/commit/ca4e1a5c9c2dd9876034318088ecab3cfdcaa127. Sorry.

[beroso]

Teamwork!

[sergeymakinen]

Okay, cheers for everyone! :)