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

carlosatfongo · June 5, 2024, 6:45pm

Huge fix, thank you for pointing this out! (Mautic v4.4.12)

arnoldmatt · June 6, 2024, 7:49am

I understand how frustrating slow report loading times can be. We’ve faced similar issues, and one thing that helped us was optimizing our database and archiving old data. You might also consider upgrading your PHP version if possible. Hope this helps a bit!

system · June 7, 2024, 7:50pm

This topic was automatically closed 36 hours after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Always long running queries against email_stats (5+ hours) Product Support	10	776	May 13, 2022
Extremly slow Dashboard Product Support	9	355	May 11, 2017
Mautic performance issue with dashboards Product Support	1	911	November 18, 2019
Mautic Speed Performance Product Support	21	3818	April 12, 2021
Import speed issue Product Support mautic-3 , mautic-4	14	531	October 1, 2023

Reports need long time to load

Related topics