OpenEMM 6.0.1, slow SQL queries when browsing mailing list

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

sp
Posts: 21
Joined: Mon Apr 12, 2010 2:04 pm

OpenEMM 6.0.1, slow SQL queries when browsing mailing list

Post by sp »

When browsing the mailing list, two SQL statements are fired. Both are very slow (around 80 second eachs on my development machine) and mysqld consumes 100% of one core. That's for just around 8500 mailings.

1. query:

Code: Select all

mysql> select count(*) from (  SELECT *, case when senddate is null then 0 else 1 end as send_null  FROM (   SELECT a.mailing_id , a.shortname  , a.description ,   min(c.change_date) senddate, m.shortname mailinglist  FROM  (mailing_tbl  a LEFT JOIN mailing_account_tbl c ON (a.mailing_id=c.mailing_id AND c.status_field='W'))  LEFT JOIN mailinglist_tbl m ON (  a.mailinglist_id=m.mailinglist_id AND  a.company_id=m.company_id)   WHERE a.company_id = 1 AND a.deleted<>1 AND a.is_template=0  AND  mailing_type in (0)   GROUP BY  a.mailing_id, a.shortname, a.description, m.shortname ) openemm ORDER BY  send_null ASC, senddate DESC, mailing_id DESC  ) agn
    -> ;
+----------+
| count(*) |
+----------+
|     8469 |
+----------+
1 row in set (1 min 20.89 sec)
2. query:

Code: Select all

mysql> SELECT *, case when senddate is null then 0 else 1 end as send_null  FROM (   SELECT a.mailing_id , a.shortname  , a.description ,   min(c.change_date) senddate, m.shortname mailinglist  FROM  (mailing_tbl  a LEFT JOIN mailing_account_tbl c ON (a.mailing_id=c.mailing_id AND c.status_field='W'))  LEFT JOIN mailinglist_tbl m ON (  a.mailinglist_id=m.mailinglist_id AND  a.company_id=m.company_id)   WHERE a.company_id = 1 AND a.deleted<>1 AND a.is_template=0  AND  mailing_type in (0)   GROUP BY  a.mailing_id, a.shortname, a.description, m.shortname ) openemm ORDER BY  send_null ASC, senddate DESC, mailing_id DESC   LIMIT 0 , 20;
+------------+---------------------------------------------------------------------+------------------------------+----------+------------------------------------------+-----------+                                                                                                                                                                                   
| mailing_id | shortname                                                           | description                  | senddate | mailinglist                              | send_null |                                                                                                                                                                                   
+------------+---------------------------------------------------------------------+------------------------------+----------+------------------------------------------+-----------+                                                                                                                                                                                   

DATA STRIPPED

+------------+---------------------------------------------------------------------+------------------------------+----------+------------------------------------------+-----------+
20 rows in set (1 min 21.02 sec)
By creating the following index, the queries run in just about half a second.
It would be nice to see such an index in an upstream release.

Code: Select all

CREATE INDEX mailing_account_tbl___mailing_id ON mailing_account_tbl (mailing_id);
Hope it helps.
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Well, "just 8,500 mailings" is a neat understatement. Thanks for your hint, we will test it and include it in the next release.
OpenEMM Maintainer
Post Reply