Slow queries when finding the revisions of a single entity.

[SanderVerkuil]

Q	A
auditor version	1.2.0
PHP version	7.4.19
Database	MySQL 5.7

Summary

When running queries like:

SELECT *
FROM audit_User
WHERE object_id = 1

Explained:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	audit_User	<null>	ALL	object_id_875a3855f585b727b04ee34ace463a77_idx	<null>	<null>	<null>	72046910	10	Using where; Using filesort

the queries take quite some time to run when a lot of data is present.

Current behavior

The queries are slow.

How to reproduce

1. Have an audited entity
2. Insert 500 entities in a table
3. Modify each entity 500 times
4. Observe that there are 500*500 revisions in the audit table (250000 records)
5. Run the query SELECT * FROM audit_Entity WHERE object_id = 1
6. See the execution plan and it should show that a FULL INDEX SCAN is done

Expected behavior

I expected that the Database would be smart and use the index to speed up this query.
For some reason though, the database thinks that the cardinality of the index is too high and a full index scan is actually faster.

Possible solution

The SimpleThings EntityAudit uses basically the same structure but has a different index setup.
Instead of creating just a UNIQUE index on the object_id it added a joined index on both the object_id and theid.
I tried to create a UNIQUE index on the id and the object_id as the id is a primary key I expected the id to be auto-incremented and unique, but I got an error that a duplicated key existed.
Perhaps this has something to do with #44?

Creating a non-unique index doesn't solve this issue, unfortunately.

The UI itself is fast because it limits the results to the 50 latest results. In the database, I'd like to see a complete overview of all the records (DataGrip limits the results to 1,500 by default) but that is quite slow.

What can be done to increase the database performance of the queries, as we really like this bundle but would also like to use this for investigative purposes.

[DamienHarper]

@SanderVerkuil #44 is fixed on master branch.
May you retry to set a composite unique index on id and object_id columns?

Nevertheless, it's quite hard to give hints on how to increase performance regarding queries without a better understanding of what queries you'd like to run and more precisely what where clauses you'd like to use. You can get very good results by using composite indices spanning on columns used in your where clauses.

[SanderVerkuil]

Ah allright, prior to that I might have to either truncate all the current tables that exist, or at the very least remove all the duplicated ID's, which might take a while.

But it might very well be the case that MySQL didn't like the fact that there were quite some duplicate ID's present, and that it would just perform a full table scan then.

And indeed, when I'm running the query as called in the bundle itself:

SELECT *
FROM audit_Entity
WHERE object_id = 865581
ORDER BY created_at DESC, id DESC
LIMIT 50

It isn't really fast, but it isn't slow either (not waiting 5 minutes for the results, but rather 45 seconds to fetch 8 results). I'll get back to you at a later time.

[DamienHarper]

@SanderVerkuil Ok, keep me updated of your findings once the tables are cleaned or truncated.

[DamienHarper]

@SanderVerkuil Any news on this?

[DamienHarper]

@SanderVerkuil I close this issue for now, feel free to reopen if needed.