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)
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)
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);