Huge table aod_indexevent

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hey guys,

so I went through the database and found out that this table is crazy huge. It has 380.000 lines and its 180MB big… I guess its indexing for search function right? Is it possible that it also indexes oauth2 tokens? Because we have a problem with one of our partners - they are sending some data to our CRM and they have a bug there and they are creating huge numbers of oauth tokens (currently 130.000 are stored there) so it would exmplain why the index is also this big…

Have you ever seen something like that?

2

This post might be helpful

https://pgorod.github.io/Database-tables-size/

although it doesn’t say much specifically about aod_indexevent.

I guess you’ll have to clean it up manually with some SQL.

Something like this, once for each module:

SELECT * FROM `aod_indexevent`
WHERE records_module = 'Leads'
  AND NOT EXISTS(
        SELECT 1 FROM leads 
        WHERE leads.id = record_id and leads.deleted = 0
         )

This gets the rows that reference records that no longer exist. You can change that “SELECT *” into a “DELETE”. Use at your own risk. Make full backups before trying.

I never deleted records from this table - if I were you I would try some smaller tests, deleting just one record, seeing how that appears when you search, etc. These are indexing events, not the actual index, I am not sure how this relates to search. But I think it’s very likely ok to remove entries about past records.

1 Like
3

Hi pgr,

thank you very much. Best reply as always :wink: Thank you and I will try that!

1 Like