Table ext_log_entries very very slow on queries about doctrineextensions HOT 7 OPEN

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.

from doctrineextensions.

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.

from doctrineextensions.

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

from doctrineextensions.

@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,

from doctrineextensions.

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

from doctrineextensions.

@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

?

from doctrineextensions.

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.

from doctrineextensions.