#105454Analyze Database Structure is super slow
Description
Using mariadb 10.4 a dozen information_schema queries are performed during database structure analysis.
(one query for every available index).
Webserver setup
php -S 127.0.0.1:8001 -t public
Database setup:
podman run -it --rm --name some-mariadb --env MARIADB_ROOT_PASSWORD=my-secret-pw -p 3306:3306 mariadb:10.4
Database config:
'DB' => [
'Connections' => [
'Default' => [
'charset' => 'utf8mb4',
'dbname' => 'subdircomposer3',
'driver' => 'mysqli',
'host' => '127.0.0.1',
'password' => 'my-secret-pw',
'port' => 3306,
'user' => 'root',
],
],
],
Analyze Database Structure query needs 27 seconds:
slow-database-analyzer.png
Queries performed:
grep "Execute.*information_schema.STATISTICS" /tmp/query.log | wc -l 352
Example query:
452 Execute SELECT NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, COLUMN_NAME AS Column_Name, SUB_PART AS Sub_Part, INDEX_TYPE AS Index_Type FROM information_schema.STATISTICS WHERE TABLE_NAME = 'be_sessions' AND TABLE_SCHEMA = 'subdircomposer3' AND `INDEX_NAME` = 'PRIMARY' ORDER BY SEQ_IN_INDEX ASC
This query is performed for every index that has already been retrieved before with the same amount of information by doctrine/dbal internally (just without the `INDEX_NAME` restriction):
452 Execute SELECT TABLE_NAME, NON_UNIQUE AS Non_Unique,
INDEX_NAME AS Key_name,
COLUMN_NAME AS Column_Name,
SUB_PART AS Sub_Part,
INDEX_TYPE AS Index_Type
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'subdircomposer3' ORDER BY SEQ_IN_INDEX
Expected number of queries: 1
All index information can be retrieved from information_schema.STATISTICS with one query, as doctrine/dbal already does, see https://github.com/doctrine/dbal/pull/5268