# Deleting old database records (from campaign\_lead\_event\_log) due to extreme database CPU usage

**URL:** <https://forum.mautic.org/t/deleting-old-database-records-from-campaign-lead-event-log-due-to-extreme-database-cpu-usage/24933>\
**Category:** Product Support\
**Created:** [August 1, 2022, 5:44pm UTC](https://forum.mautic.org/t/deleting-old-database-records-from-campaign-lead-event-log-due-to-extreme-database-cpu-usage/24933 "2022-08-01T17:44:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![instamaker](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/instamaker/32/481_2.png) [@instamaker](https://forum.mautic.org/u/instamaker)\
**Post date:** [August 1, 2022, 5:44pm UTC](https://forum.mautic.org/t/deleting-old-database-records-from-campaign-lead-event-log-due-to-extreme-database-cpu-usage/24933/1 "2022-08-01T17:44:24Z")

</div>

**Your software**  
My Mautic version is: 3.2.0  
My PHP version is: 7.3.26  
My Database type and version is: MySQL v8

**Your problem**  
I posted some context [here](https://forum.mautic.org/t/mautic-database-is-huge-can-i-manually-delete-old-records/22646), but this is a different question. Essentially, my mautic tables are huge — email\_stats at 25GB, page\_hits at 15GB, campaign\_lead\_event\_log at 9GB, and lead\_event\_log at 3GB.

The DB size is no problem to me (I’ve been cleaning old records in page\_hits and email\_stats using advice in the original thread — anyone who wants to reduce the size of their DB, check that [original thread](https://forum.mautic.org/t/mautic-database-is-huge-can-i-manually-delete-old-records/22646) out for some great advice!).

However, the Mautic cronjobs that execute campaigns also run mySQL queries that are hogging up a 90%+ of my CPU and result in an extreme amount of disk reads. My entire server goes extremely slow every time the cronjobs are scheduled.

From looking into the mysql queries, it seems like the problem here is the campaign\_lead\_event\_log table.

**I would like to clean up and reduce the size of the campaign\_lead\_event\_log table** — there are events in the table from 3 years ago, but only the last 2-3 months are relevant for campaigns.

I would like some advice on how to clean up that table which doesn’t result in any inconsistencies or issues in Mautic. Perhaps some help on the right SQL query I can use.

On the original thread, there was [a post](https://forum.mautic.org/t/mautic-database-is-huge-can-i-manually-delete-old-records/22646/2) from @ekke that said:

> “Most importantly: make sure that for every lead the youngest entry in campaign\_lead\_event\_log does NOT get deleted!”

There was also [a suggestion](https://forum.mautic.org/t/mautic-database-is-huge-can-i-manually-delete-old-records/22646/10) from @sfahrenkrog1 on a query that could do this (deletes the last 30 days of data while keeping the youngest record). Here it is:

```auto
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 )

```

I wanted to ask if 1) there is anyone who understands where campaign\_lead\_event\_log is used, and 2) if so, is that query above ok to execute, and 3) is there anything else I need to be aware of when cleaning out the campaign\_lead\_event\_log table?

I’d really appreciate any advice. Thank you!

---

<div class="post-metadata">

**Author:** ![sfahrenkrog1](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/sfahrenkrog1/32/4583_2.png) [@sfahrenkrog1](https://forum.mautic.org/u/sfahrenkrog1)\
**Post date:** [August 2, 2022, 7:46am UTC](https://forum.mautic.org/t/deleting-old-database-records-from-campaign-lead-event-log-due-to-extreme-database-cpu-usage/24933/2 "2022-08-02T07:46:09Z")

</div>

HI instamaker

We use this query in production to cleanup the campaign\_lead\_event\_log:

```auto
# cleanup campaign_lead_event_log after 30 days 
DELETE clel FROM campaign_lead_event_log clel WHERE date_triggered < (NOW() - INTERVAL 30 DAY) and clel.id not in ( SELECT max_id FROM ( select max(id) as max_id FROM campaign_lead_event_log group by lead_id, campaign_id) AS tmptable );

```

and to free up the space:

```auto
OPTIMIZE LOCAL TABLE campaign_lead_event_log;

```

The campaign\_lead\_event\_log “logs” every action a lead executed in the campaign. But the term “log” is confusing because it is not just logging.

Mautic checks in this table if the campaign action was already executed for the lead!  
So: if you delete the wrong ones, you could trigger another execution of the campaign action for the lead.  
Worst case scenario: Leads getting emails again from a campaign action … etc.

(That was the warning from ekke)

The solution to cleanup the table:  
Don’t delete the last logged action per campaign per lead. Then nothing happens.

If you want to be safe then maybe restrict the query to old campaigns which are no longer published?

Greetings  
Sebastian

---

<div class="post-metadata">

**Author:** ![instamaker](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/instamaker/32/481_2.png) [@instamaker](https://forum.mautic.org/u/instamaker)\
**Post date:** [August 2, 2022, 11:28am UTC](https://forum.mautic.org/t/deleting-old-database-records-from-campaign-lead-event-log-due-to-extreme-database-cpu-usage/24933/3 "2022-08-02T11:28:41Z")

</div>

Hey Sebastian, appreciate the thoughtful reply and explanation. That clears things up — I’m going to go in and clear that table now 🙂