Table ext_log_entries very very slow on queries

[wehostadm]

Environment

Windows 10 with MySQL

Package

show

$ composer show --latest gedmo/doctrine-extensions
# Put the result here.
name     : gedmo/doctrine-extensions
descrip. : Doctrine behavioral extensions
keywords : Blameable, behaviors, doctrine, extensions, gedmo, loggable, nestedset, odm, orm, sluggable, sortable, timestampable, translatable, tree, uploadable
versions : * v3.15.0
latest   : v3.15.0
type     : library
license  : MIT License (MIT) (OSI approved) https://spdx.org/licenses/MIT.html#licenseText
homepage : http://gediminasm.org/
source   : [git] https://github.com/doctrine-extensions/DoctrineExtensions.git 2a89103f4984d8970f3855284c8c04e6e6a63c0f
dist     : [zip] https://api.github.com/repos/doctrine-extensions/DoctrineExtensions/zipball/2a89103f4984d8970f3855284c8c04e6e6a63c0f 2a89103f4984d8970f3855284c8c04e6e6a63c0f
path     : C:\Users\florent\Desktop\wehost\octopus-v2\vendor\gedmo\doctrine-extensions
names    : gedmo/doctrine-extensions

support
email : [email protected]
issues : https://github.com/doctrine-extensions/DoctrineExtensions/issues
source : https://github.com/doctrine-extensions/DoctrineExtensions/tree/v3.15.0
wiki : https://github.com/Atlantic18/DoctrineExtensions/tree/main/doc

autoload
psr-4
Gedmo\ => src/

requires
behat/transliterator ^1.2
doctrine/collections ^1.2 || ^2.0
doctrine/common ^2.13 || ^3.0
doctrine/deprecations ^1.0
doctrine/event-manager ^1.2 || ^2.0
doctrine/persistence ^2.2 || ^3.0
php ^7.4 || ^8.0
psr/cache ^1 || ^2 || ^3
psr/clock ^1
symfony/cache ^5.4 || ^6.0 || ^7.0

requires (dev)
doctrine/annotations ^1.13 || ^2.0
doctrine/cache ^1.11 || ^2.0
doctrine/dbal ^3.2
doctrine/doctrine-bundle ^2.3
doctrine/mongodb-odm ^2.3
doctrine/orm ^2.14.0
friendsofphp/php-cs-fixer ^3.14.0
nesbot/carbon ^2.71 || ^3.0
phpstan/phpstan ^1.10.2
phpstan/phpstan-doctrine ^1.0
phpstan/phpstan-phpunit ^1.0
phpunit/phpunit ^9.6
rector/rector ^0.19
symfony/console ^5.4 || ^6.0 || ^7.0
symfony/phpunit-bridge ^6.0 || ^7.0
symfony/yaml ^5.4 || ^6.0 || ^7.0

suggests
doctrine/mongodb-odm to use the extensions with the MongoDB ODM
doctrine/orm to use the extensions with the ORM

conflicts
doctrine/annotations <1.13 || >=3.0
doctrine/dbal <3.2 || >=4.0
doctrine/mongodb-odm <2.3 || >=3.0
doctrine/orm <2.14.0 || 2.16.0 || 2.16.1 || >=3.0

Doctrine packages

show

$ composer show --latest 'doctrine/*'
# Put the result here.
Color legend:
- patch or minor release available - update recommended
- major release available - update possible
- up to date version
doctrine/annotations                2.0.1  2.0.1  Docblock Annotations Parser
doctrine/cache                      2.2.0  2.2.0  PHP Doctrine Cache library is a popular cache implementation that...
doctrine/collections                2.2.1  1.8.0  PHP Doctrine Collections library that adds additional functionali...
doctrine/common                     3.4.3  3.4.3  PHP Doctrine Common project is a library that provides additional...
doctrine/data-fixtures              1.7.0  1.7.0  Data Fixtures for all Doctrine Object Managers
doctrine/dbal                       3.8.3  3.8.3  Powerful PHP database abstraction layer (DBAL) with many features...
doctrine/deprecations               1.1.3  1.1.3  A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3...
doctrine/doctrine-bundle            2.12.0 2.12.0 Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle   3.5.1  3.5.1  Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle 3.3.0  3.3.0  Symfony DoctrineMigrationsBundle
doctrine/event-manager              2.0.0  1.2.0  The Doctrine Event Manager is a simple PHP event system that was ...
doctrine/inflector                  2.0.10 2.0.10 PHP Doctrine Inflector is a small library that can perform string...
doctrine/instantiator               2.0.0  1.5.0  A small, lightweight utility to instantiate objects in PHP withou...
doctrine/lexer                      3.0.1  2.1.1  PHP Doctrine Lexer parser library that can be used in Top-Down, R...
doctrine/migrations                 3.7.4  3.5.5  PHP Doctrine Migrations project offer additional functionality on...
doctrine/orm                        2.19.3 2.19.3 Object-Relational-Mapper for PHP
doctrine/persistence                3.3.2  3.3.2  The Doctrine Persistence project is a set of shared interfaces an...
doctrine/sql-formatter              1.2.0  1.2.0  a PHP SQL highlighting library

PHP version

$ php -v
# Put the result here.
PHP 8.1.1 (cli) (built: Dec 15 2021 10:36:13) (NTS Visual C++ 2019 x64)
Copyright (c) The PHP Group
Zend Engine v4.1.1, Copyright (c) Zend Technologies
    with Zend OPcache v8.1.1, Copyright (c), by Zend Technologies

Subject

Minimal repository with the bug

Nothing

Steps to reproduce

Have a huge amount of data in ext_log_entries table (around 700 000 for me)

The query :
SELECT DISTINCT e0.id AS id_0, e0_.id AS id_1 FROM ext_log_entries e0_ WHERE (e0_.id = 818043 OR LOWER(e0_.action) LIKE '%818043%' OR LOWER(e0_.object_id) LIKE '%818043%' OR LOWER(e0_.object_class) LIKE '%818043%' OR e0_.version = 818043 OR LOWER(e0_.data) LIKE '%818043%' OR LOWER(e0_.username) LIKE '%818043%') AND e0_.username IS NOT NULL ORDER BY e0_.id DESC LIMIT 30;_

Expected results

Having a query to search a log quick

Actual results

The query to search a log takes around 8 seconds or more

[mbabker]

What's generating this query? Is it something in your application? Is it something in this package? If it's in this package, what is it?

Because you're doing wildcard LIKE conditions on every column in the table, I don't find it too surprising that it's somewhat slow and this query would be better written only targeting needed columns, and for columns with fixed values (i.e. the action column can only be one of a handful of strings) making sure you're only querying for one of those values.

[Chris53897]

I changed the query a bit to run on console for postgres 16.

FROM ext_log_entries
WHERE (id = 100 OR LOWER(action) LIKE '%100%' OR LOWER(object_id) LIKE '%100%' OR LOWER(object_class) LIKE '%100%' OR version = 100 OR LOWER(data) LIKE '%100%' OR LOWER(username) LIKE '%100%') AND username IS NOT NULL
ORDER BY id DESC
LIMIT 30;```

370.000 Entries. It took 55ms

This is not an issue of this repo.

[ytilotti]

@wehostadm, the field action is not indexed by default.

[wehostadm]

@mbabker It is the Easyadmin Bundle that do this query, I have no control of that :(

@ytilotti How can I add the index on "action" field in Symfony because I do not managed the Gedmo\Loggable\Entity\LogEntry that is inside this bundle ?

Thanks all,

[ytilotti]

@wehostadm you manage the structure of LogEntry via migrations doctrine.

[wehostadm]

@ytilotti Only for entities that I managed not for those inside Bundles. Do I need to create an Entity that inherits Gedmo\Loggable\Entity\LogEntry ?

Somethink like :
#[ORM\Index(name: 'action_lookup_idx', columns: ['action'])]
class MyLogEntity extends Gedmo\Loggable\Entity\LogEntry

?

[mbabker]

I don't use EasyAdmin so I don't know how much help I could offer here, but based on ~5 minutes of looking at a couple of screenshots and the filters docs, it seems like there'd be ways to better build the filters for a log entry list (as I'm assuming you've added a section that lets you browse Gedmo\Loggable\Entity\LogEntry records).

• For the action field, use a ChoiceFilter instead of the default text filter so filtering on that column is narrowed down to one of the known allowed values and not a LIKE condition
• Similar for the object_class field, I'd use a ChoiceFilter where the options are the list of entities that you have logging enabled for (and even if it's all of your app's entities, the choice filter would still write a better query than a LIKE condition)

The worst field to try and support filtering for is the data field because it's just an inconsistently serialized array, that's just never going to create a great query.

As far as the custom entity goes, yes, that should do the trick. The other thing is to make sure you've added the logEntryClass config to the attribute on all of your loggable entities so the extension knows to use your custom class, otherwise it'll continue using the default one from here.