Index on mailing_account_tbl
Posted: Tue Jan 19, 2016 10:46 am
Hello
we found that the table mailing_account_tbl should be indexed, since it's a big table.
If not and having a very big number of mailings, the console never answers, since the SQL request in openemm 2013 for mailing overview seems to be something like :
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
GROUP BY a.mailing_id, a.shortname, a.description, m.shortname ) openemm
ORDER BY send_null ASC, senddate DESC, mailing_id DESC;
we found that the table mailing_account_tbl should be indexed, since it's a big table.
If not and having a very big number of mailings, the console never answers, since the SQL request in openemm 2013 for mailing overview seems to be something like :
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
GROUP BY a.mailing_id, a.shortname, a.description, m.shortname ) openemm
ORDER BY send_null ASC, senddate DESC, mailing_id DESC;