Reports need long time to load

Madlen · March 10, 2023, 2:16pm

Your software
My Mautic version is: 4.4.1
My PHP version is: 7.4.3
My Database type and version is: mysql 8.0.32

Your problem
We have a large Mautic instance with a high volume of emails sent every month. We’ve created a report for emails (including columns like sent count, click ration, bounce ratio, published up, …).
Opening the report takes around 10 minutes which of course is very inconvenient.

Is anyone encountering similar problems and what are your ideas to improve this?

These errors are showing in the log: -

Steps I have tried to fix the problem: -

mikew · March 23, 2023, 10:13am

Yes the reporting is super slow inside Mautic, especially when you start to get to medium/big data.

Check out a previous post of mine where we implement a different reporting software inside Mautic: Surge Mautic Dashboard into something Amazing!

joeyk · April 26, 2023, 9:02am

This should be the fix:

github.com/mautic/mautic

Missing INDEX makes report generation extremly slow (FIX included)

opened 03:49PM - 29 Mar 23 UTC

Moongazer

bug reports needs-triage performance-scalability

### Mautic Version 4.4.x series ### PHP version 7.4 ### What browser…s are you seeing the problem on? Not relevant ### What happened? ## Issue If the `lead_donotcontact` table has a lot of entries (e.g. 60.000), the report-generation for `emails` becomes extremly (unusable) slow, if the bounced or unsubscribed columns are selected. We tracked down the issue to the following sub-query, which is added e.g. when the `bounced` column is selected for an email report (same applies for `unsubcribed`): ``` IFNULL((SELECT ROUND(SUM(IF(dnc.id IS NOT NULL AND dnc.channel_id=e.id AND dnc.reason=2 , 1, 0)), 1) FROM lead_donotcontact dnc), 0) ``` While checking if all the fields inside the condition having an index, we found out that an index for `channel_id` is missing. ## Solution To test it, we manually added the index, which increased the query speed for the email report by ~90%: ``` ALTER TABLE lead_donotcontact ADD INDEX dnc_channel_id_serch (channel_id); ``` As permanent solution, please add `->addIndex(['channel_id'], 'dnc_channel_id_search')` in the following line: https://github.com/mautic/mautic/blob/d7a7c3e90afe8ee82e3f66585f8641d8b231fa60/app/bundles/LeadBundle/Entity/DoNotContact.php#L73 ### How can we reproduce this issue? ## Step 1 Take a huge Mautic database, e.g.: leads: 1.500.000 lead_donotcontact: 60.000 emails: 200 email_stats: 3.500.000 ## Step 2 - Create an email-report - Add the following columns: `Read count, Read ratio, Clicks, Clicks ratio, Unique clicks, Unique clicks ratio` - Add the following columns to slow down the query drastically: `bounced, unsubscribed` - Add filter: `Sent count` greather than `100` (dynamic yes) ### Relevant log output _No response_ ### Code of Conduct - [X] I confirm that I have read and agree to follow this project's Code of Conduct

Joey