Extremely slow query execution

[girish1605]

What type of report is this:

Q	A
Bug report?	N
Feature request?	N
Enhancement?	Y

Description:

We Imported approx 1 million contacts, after which Mautic became unusable, with most pages taking forever to load. We monitored the queries being executed in MySQL and created an index on few of the tables/columns that improved things a bit. In particular, we added index to these tables/columns:

1. ma_email_stats/ date_read
2. ma_email_stats/is_read
3. ma_lead_lists_leads/manually_removed

This improved things significantly, and we are able to open most pages even though its takes a bit of time.

However, there is one more page (channels -> emails) that still refuses to open (takes about 20 mins to open). We observed the queries that this page is executing and found this one to be taking most of that 20 mins of execution time:

Original Query

SELECT COUNT
  (l.id) AS COUNT
FROM
  ma_leads l
WHERE
  (
    NOT EXISTS(
    SELECT NULL
  FROM
    ma_lead_donotcontact dnc
  WHERE
    (dnc.lead_id = l.id) AND(dnc.channel = "email")
  )
  ) AND(
    NOT EXISTS(
    SELECT NULL
  FROM
    ma_email_stats stat
  WHERE
    (stat.lead_id = l.id) AND(stat.email_id = 69)
  )
  ) AND(
    EXISTS(
    SELECT NULL
  FROM
    ma_lead_lists_leads ll
  WHERE
    (ll.leadlist_id IN(35)) AND(ll.lead_id = l.id) AND(ll.manually_removed = 0)
  )
  ) AND(
    (l.email IS NOT NULL) AND(l.email <> '')
  )

Modified Query

We re-wrote the above co-related query to this, which uses a simple join instead and runs in under 5 seconds. However, we are not sure how to make Mautic use this new query instead:

select count
(l.id) as count
from ma_leads l
where 
l.id not in (SELECT dnc.lead_id
  FROM
    ma_lead_donotcontact dnc, ma_leads ml1
  WHERE
    (dnc.lead_id = ml1.id) AND(dnc.channel = "email"))
    and 
    l.id not in (
    SELECT stat.lead_id
  FROM
    ma_email_stats stat, ma_leads ml2
  WHERE
    (stat.lead_id = ml2.id) AND(stat.email_id = 69)
  )
  and l.id in (
  SELECT ll.lead_id
  FROM
    ma_lead_lists_leads ll
  WHERE
    (ll.leadlist_id IN(35)) AND(ll.lead_id = l.id) AND(ll.manually_removed = 0)
  )
  and 
    ((l.email IS NOT NULL) AND(l.email <> ''))

If a bug:

Q	A
Mautic version	1.4.1
PHP version	5.5.30

Steps to reproduce:

1. Import nearly 1 million contacts
2. Try to open channels -> emails page

Log errors:

Please check for related errors in the latest log file in [mautic root]/app/log/ and/or the web server's logs and post them here. Be sure to remove sensitive information if applicable.

[kinged007]

+1

[npracht]

@escopecz would you carry an PR about that? If it improve mautic performances, it shouldn't stay like that knowing what has been tested and code almost ready right?

[PatchRanger]

I think that's quite urgent, because at some scale it is making usage of Mautic really annoying.
Our userbase is relatively small, our Mautic database is about 8GB - but nevertheless we had to ask our hosting provider to apply archive pricing plan for our case, because it has extended database query limit. And now we are again facing the same issue: hoster sometimes rejects Mautic queries because of very heavy database load.
@escopecz Please rise the priority of performance issues.

[escopecz]

There is no button to raise the priority. If there is the solution why anyone hasn't sent a PR with it? Is there some catch?

[ninjoan]

I have the same problem but with 600 contacts

[zenzozen]

Same here. with 300k contacts.
Open calendar, import contacts, open any report freezes mautic.

[akarokr]

I have the same problem with 600k+ contacts. Email channel doesn't open.

[howlinghuffy]

+1 for this issue, it's the main issue preventing us from scaling Mautic. Is anyone working on implementing the solution for this? And if not, can someone direct me to where these queries are generated so I can implement the fix?

[craigsketchley]

Yeah, +1 for this. Any reason this was closed @dbhurley? Happy to work on this and contribute a solution if needed.

[dbhurley]

Excellent! Thanks @craigsketchley :) (and others). Needed action to be taken or would be closed to due inactivity. I've reopened. Thanks for the replies everyone!

[howlinghuffy]

Hey @dbhurley, I have a PR up to fix this issue #5405. Makes loading on the emails page 200 times faster with a large database of users. Ready for review - let me know if any amendments required.

[dbhurley]

That's awesome! I'll take a look and I'm sure others will as well. 😎