Index on mailing_account_tbl

Use this forum for questions regarding adoption and functionality of OpenEMM

Moderator: moderator

Index on mailing_account_tbl

Postby chollois » 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;
chollois
 
Posts: 7
Joined: Fri Sep 18, 2015 2:00 pm

Re: Index on mailing_account_tbl

Postby jlesaux » Fri Feb 05, 2016 2:37 pm

Hi,

Maybe you can test that :
Code: Select all
CREATE INDEX mailing_account_tbl_idx ON mailing_account_tbl (mailing_id, status_field);


Should works on your issue.

Best regards,

--
Julien LE SAUX
jlesaux
 
Posts: 8
Joined: Wed Aug 27, 2014 4:25 pm

Re: Index on mailing_account_tbl

Postby chollois » Fri Feb 05, 2016 2:38 pm

Perfect, thank you
chollois
 
Posts: 7
Joined: Fri Sep 18, 2015 2:00 pm

Re: Index on mailing_account_tbl

Postby maschoff » Sat Feb 06, 2016 10:34 pm

Thanks for sharing, I will add this tip to the next release of the OpenEMM Install&Admin Guide.
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2131
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany

Re: Index on mailing_account_tbl

Postby jlesaux » Thu Feb 25, 2016 11:26 am

Hi,

OK cool !

You may want to add this one too :
Code: Select all
CREATE INDEX mailing_id_index ON component_tbl (mailing_id);


Best regards,

--
Julien LE SAUX
jlesaux
 
Posts: 8
Joined: Wed Aug 27, 2014 4:25 pm

Re: Index on mailing_account_tbl

Postby maschoff » Tue Apr 12, 2016 9:46 am

Will do, thanks!
OpenEMM Maintainer
maschoff
Site Admin
 
Posts: 2131
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany


Return to Using OpenEMM

Who is online

Users browsing this forum: No registered users and 3 guests

cron