Become very slow after update from 2.0.5 to 2.0.14 #15776

rosancoderian · 2018-02-26T08:33:07Z

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

The text was updated successfully, but these errors were encountered:

lubosdz · 2018-02-26T08:50:06Z

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 · 2018-02-26T09:10:39Z

Also please try 2.0.13.

yii-bot · 2018-02-26T09:10:50Z

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 · 2018-02-26T09:45:24Z

@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 · 2018-02-26T09:55:17Z

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

samdark · 2018-02-26T09:56:38Z

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 · 2018-02-26T10:49:26Z

the problems occured only in version 2.0.13 and above

samdark · 2018-02-26T11:05:58Z

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

sergeymakinen · 2018-02-26T19:30:42Z

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

hooman-mirghasemi · 2018-02-27T07:46:32Z

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

SilverFire · 2018-02-27T07:52:26Z

@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 · 2018-02-28T10:40:55Z

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

samdark · 2018-02-28T11:10:07Z

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

hooman-mirghasemi · 2018-02-28T11:19:52Z

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 · 2018-02-28T11:28:36Z

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 · 2018-02-28T12:06:06Z

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 · 2018-02-28T12:27:18Z

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 · 2018-02-28T12:53:00Z

@Eseperio which DB server is that? MySQL?

Eseperio · 2018-02-28T12:56:17Z

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

samdark · 2018-02-28T12:58:03Z

@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 · 2018-02-28T12:58:54Z

Yes I use mysql too and store Session in Db

Eseperio · 2018-02-28T13:02:03Z

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 · 2018-02-28T13:22:06Z

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

upsert method was introduced here de17502

Eseperio · 2018-02-28T13:29:30Z

@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 · 2018-02-28T13:37:12Z

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

Eseperio · 2018-02-28T14:10:26Z

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 · 2018-02-28T15:35:02Z

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 · 2018-03-03T18:51:04Z

Added docs

MartijnHols · 2018-03-06T15:53:11Z

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 · 2018-03-06T16:42:37Z

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 · 2018-03-06T17:13:18Z

Is that MySQL, @MartijnHols?

MartijnHols · 2018-03-06T21:32:31Z

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 · 2018-03-07T10:13:03Z

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 · 2018-03-07T10:50:01Z

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 · 2018-03-07T12:21:23Z

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 · 2018-03-07T14:40:20Z

@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 · 2018-03-07T14:49:58Z

@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 · 2018-03-07T14:57:11Z

(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 · 2018-03-07T15:17:18Z

Awesome!

sergeymakinen · 2018-03-07T16:58:51Z

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 · 2018-03-07T22:23:06Z

@sergeymakinen did not you mean berosoboy? ;)

samdark · 2018-03-07T22:40:43Z

@berosoboy ca4e1a5. Sorry.

…og [skip ci]

beroso · 2018-03-07T22:41:58Z

Teamwork!

sergeymakinen · 2018-03-08T10:30:48Z

Okay, cheers for everyone! :)

samdark added the status:need more info label Feb 26, 2018

rosancoderian mentioned this issue Feb 27, 2018

Unable to verify your data submission since updated to 2.0.14 #15783

Closed

SilverFire added this to the 2.0.14.2 milestone Feb 27, 2018

samdark added type:docs Documentation and removed status:need more info labels Feb 28, 2018

SilverFire closed this as completed in ab8e1bc Mar 3, 2018

SilverFire reopened this Mar 6, 2018

samdark added type:bug Bug and removed type:docs Documentation labels Mar 7, 2018

sergeymakinen mentioned this issue Mar 7, 2018

Speed up fetching MySQL constraints (fixes #15776) #15851

Merged

samdark closed this as completed Mar 7, 2018

samdark pushed a commit that referenced this issue Mar 7, 2018

Fixes #15776: Fixed slow MySQL constraints retrieving

9221386

samdark added a commit that referenced this issue Mar 7, 2018

#15776: Removed unnecessary UPGRADE note, added @berosoboy to changel…

ca4e1a5

…og [skip ci]

brandonkelly mentioned this issue Jun 29, 2018

deprecationerrors query hangs for 10-20 seconds craftcms/cms#2539

Closed

galieth mentioned this issue Jul 4, 2018

schemeCache Does not seem to be working properly. #16478

Closed

shuckster mentioned this issue Mar 23, 2019

Craft CMS super slow every now and then; information_schema problems? craftcms/cms#4037

Closed

Become very slow after update from 2.0.5 to 2.0.14 #15776

Become very slow after update from 2.0.5 to 2.0.14 #15776

Comments

rosancoderian commented Feb 26, 2018

lubosdz commented Feb 26, 2018

samdark commented Feb 26, 2018

yii-bot commented Feb 26, 2018

rosancoderian commented Feb 26, 2018

samdark commented Feb 26, 2018

samdark commented Feb 26, 2018

rosancoderian commented Feb 26, 2018

samdark commented Feb 26, 2018 • edited

sergeymakinen commented Feb 26, 2018

hooman-mirghasemi commented Feb 27, 2018

SilverFire commented Feb 27, 2018

hooman-mirghasemi commented Feb 28, 2018

samdark commented Feb 28, 2018

hooman-mirghasemi commented Feb 28, 2018 • edited

samdark commented Feb 28, 2018

Eseperio commented Feb 28, 2018 • edited

Eseperio commented Feb 28, 2018

samdark commented Feb 28, 2018

Eseperio commented Feb 28, 2018 • edited

samdark commented Feb 28, 2018 • edited

hooman-mirghasemi commented Feb 28, 2018

Eseperio commented Feb 28, 2018 • edited

Eseperio commented Feb 28, 2018

Eseperio commented Feb 28, 2018 • edited

zhukovra commented Feb 28, 2018

Eseperio commented Feb 28, 2018 • edited

sergeymakinen commented Feb 28, 2018

SilverFire commented Mar 3, 2018

MartijnHols commented Mar 6, 2018

SilverFire commented Mar 6, 2018

samdark commented Mar 6, 2018

MartijnHols commented Mar 6, 2018 • edited

MartijnHols commented Mar 7, 2018

beroso commented Mar 7, 2018 • edited

sergeymakinen commented Mar 7, 2018

MartijnHols commented Mar 7, 2018 • edited

beroso commented Mar 7, 2018 • edited

MartijnHols commented Mar 7, 2018 • edited

beroso commented Mar 7, 2018

sergeymakinen commented Mar 7, 2018 • edited

beroso commented Mar 7, 2018

samdark commented Mar 7, 2018

beroso commented Mar 7, 2018

sergeymakinen commented Mar 8, 2018

samdark commented Feb 26, 2018 •

edited

hooman-mirghasemi commented Feb 28, 2018 •

edited

Eseperio commented Feb 28, 2018 •

edited

Eseperio commented Feb 28, 2018 •

edited

samdark commented Feb 28, 2018 •

edited

Eseperio commented Feb 28, 2018 •

edited

Eseperio commented Feb 28, 2018 •

edited

Eseperio commented Feb 28, 2018 •

edited

MartijnHols commented Mar 6, 2018 •

edited

beroso commented Mar 7, 2018 •

edited

MartijnHols commented Mar 7, 2018 •

edited

beroso commented Mar 7, 2018 •

edited

MartijnHols commented Mar 7, 2018 •

edited

sergeymakinen commented Mar 7, 2018 •

edited