Mautic database is huge, can I manually delete old records?

Product Support
10

Good Morning everybody

Regarding the comment from ekke:

Most importantly: make sure that for every lead the youngest entry in campaign_lead_event_log does NOT get deleted!

I tried to adapt my sql query

My existing query was like this:
DELETE FROM lead_event_log where bundle="lead" and object="import" AND date_added < (NOW() - INTERVAL 30 DAY);

I try to build a query which selects for every lead_id, campaign_id combination the latest entry:

# select for every combination of lead_id and campaign id the last event log id 
select max(id),lead_id, campaign_id, count(id) FROM campaign_lead_event_log  group by lead_id, campaign_id order by lead_id

(i added lead_id, campaign_id and the number of items for debugging reasons in the select statement)

If you combine this with my old query you would get something like this:

# select all entries with a triggered date oder than 30 days and which are not the last entry in the log per leadid and campaign 

select * FROM campaign_lead_event_log 
	WHERE date_triggered < (NOW() - INTERVAL 30 DAY) 
	and campaign_lead_event_log.id not in ( select max(id) FROM campaign_lead_event_log  group by lead_id, campaign_id )

Of course this query needs now some debugging love.

@ekke What do you think? Is this somewhere near your own solution of the plugin you build?

Greetings
Sebastian

2 Likes