Hi there,
Can you post some more info:
Full email_stats count/size
Full email_stats count where ch.date_sent > $foo
Size of the indexes
What storage engine the database/tables are
I dont think lead_frequencyrules is being used, but that should not affect the query too much.
That query maybe coming from a campaign, that’s why that has so many leads.
If possible to run the real query with EXPLAIN, you may be able to see what part exactly it’s stuck, or strace -p in the worse case.
I don’t have too much knowledge on the internals/query tunning of mysql, but AFAIK it can only do one index a time, and that list with a lot of IN is not going to be using the index (at least if this was postgres, it will try to do a (parallelo) seq scan, that will inevitability be slow
Also, did ever tried to use MariaDB instead? Mautic can run fine on mariadb:10.5.10 with ~ 30k leads/100k emails/week with 1 year retation, but I also have some smaller mautic running on percona-server:5.7
the only time I had a CPU spike was when I was using this segment: Slow segment runtime