Issue Bug Closed

#105454Analyze Database Structure is super slow

Benjamin Franzke 10/25/24, 05:14 updated 08/12/25, 14:35

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

Reviews 2