Database optimization addon for Interspire Email Marketer

The following document describes how it works database optimization add-on for Interspire Email Marketer and how to transform a standard environment to use the add-on.

Optimization is based on database partition. For the tables events and email open (the largest tables in DB) we create a partition, old content is stored in the partition meanwhile new content is stored on the standard table (we keep 1 year data on main table, that value can be defined on add-on).

Records older than 1 year are moved to a new partition while newest content remain on main table keeping the system light to speed it up.

How to activate addon

Add-on can automatically create partitions, but that can be a long process. We recommend create initial partition manually to have deeper control and then copi addon files on interspire addon directory.

Activate “Partition add-on” like any other add-on, first install add-on and then activate it. After installation access configuration settings:

  • Number of days
  • Time of the day to make partition

 


 

How to manually create partition

First, drop current primary key

ALTER TABLE email_list_subscriber_events DROP PRIMARY KEY, ADD PRIMARY KEY(eventid,eventdate);

ALTER TABLE email_stats_emailopens DROP PRIMARY KEY, ADD PRIMARY KEY(openid,opentime);

Create partition:

ALTER TABLE email_stats_emailopens PARTITION BY RANGE(opentime) (PARTITION p0 VALUES LESS THAN (1436781011),PARTITION p1 VALUES LESS THAN MAXVALUE);

ALTER TABLE email_list_subscriber_events PARTITION BY RANGE(eventdate) (PARTITION p0 VALUES LESS THAN (1436781011),PARTITION p1 VALUES LESS THAN MAXVALUE);

Define the right time stamp

On the current example we use 1436781011 to define 1 year.

That process can take from several minutes to several hours, depending on the database size.

We recommend create a backup before start the process and also send a notification to customers because during update process service will be unavailable.

 

How to manually remove partitions

If you don’t want to use partitions anymore on your database you just need to execute the following MySQl commands to revert partitions and create original primary key.

ALTER TABLE email_list_subscriber_events REMOVE PARTITIONING;
ALTER TABLE email_list_subscriber_events DROP PRIMARY KEY, ADD PRIMARY KEY(eventid);

ALTER TABLE email_stats_emailopens REMOVE PARTITIONING;
ALTER TABLE email_stats_emailopens DROP PRIMARY KEY, ADD PRIMARY KEY(openid);

 

Change core files

Despite most part of the code is on the addon, you need to change some core files in order to use new database schema.

We have identify the files that need to be changed.

admin/functions/segment.php
Method: _getAvailableCampaigns
After: array('value' => '-1', 'text' => GetLang('FilterAnyNewsletter')),

            /* Segment by opened campaign #1*/
                array('value' => '-2', 'text' => GetLang('FilterLastNewsletter')),
                array('value' => '-3', 'text' => GetLang('FilterLastWeekNewsletters')),
                array('value' => '-4', 'text' => GetLang('FilterLastMonthNewsletters')),
                array('value' => '-5', 'text' => GetLang('FilterLast3MonthNewsletters')),
                array('value' => '-6', 'text' => GetLang('FilterLast6MonthNewsletters')),
                array('value' => '-7', 'text' => GetLang('FilterLastYearNewsletters')),
            /* End segment by opened campaign #1*/



Admin/function/api/subscribers.php
Method: _GenerateSegmentRuleQuery
After; case 'campaign': (~4115)

/* Segment by opened campaign #2*/
                            case 'campaign':
                                if ($eachRule['rules']['ruleOperator'] == 'equalto') {
                                    if (!array_key_exists('seo', $tables)) {
                                        $tables['seo'] = ', [|PREFIX|]stats_emailopens AS seo';
                                        $tables['sn'] = 'JOIN [|PREFIX|]stats_newsletters AS sn'
                                        . ' ON seo.statid = sn.statid';
                                        array_push($joins, 'subscribers.subscriberid = seo.subscriberid');
                                    }

                                    $tempNewsletterID = intval($eachRule['rules']['ruleValues'][0]);
                                    if ($tempNewsletterID == -1) {
                                        array_push($conditions, 'sn.newsletterid <> 0');
                                                                        } else if ($tempNewsletterID < -1) {
                                                                            $curdate = time();
                                                                            // If it's an advanced filter, check type
                                                                            // Check for last sent campaign for the given lists
                                                                            if ($tempNewsletterID == -2) {
                                                                                // Get last newsletter send for the given lists
                                                                                $laststatid = $this->_GetLastStatId($listids);
                                                                                
                                                                                array_push($conditions, 'sn.statid = ' . $laststatid);
                                                                                
                                                                            } else if ($tempNewsletterID == -3) {
                                                                                // Filter for last week stats
                                                                                array_push($conditions, 'seo.opentime >= ' . strtotime("-1 week", $curdate) . ' AND sn.starttime between ' . strtotime("-1 week", $curdate) . ' AND ' . $curdate);
                                                                            } else if ($tempNewsletterID == -4) {
                                                                                // Filter for last month
                                                                                array_push($conditions, 'seo.opentime >= ' . strtotime("-1 month", $curdate) . ' AND sn.starttime between ' . strtotime("-1 month", $curdate) . ' AND ' . $curdate);
                                                                            } else if ($tempNewsletterID == -5) {
                                                                                // Filter for last 3 months
                                                                                array_push($conditions, 'seo.opentime >= ' . strtotime("-3 months", $curdate) . ' AND sn.starttime between ' . strtotime("-3 months", $curdate) . ' AND ' . $curdate);
                                                                            } else if ($tempNewsletterID == -6) {
                                                                                // Filter for last 6 months
                                                                                array_push($conditions, 'seo.opentime >= ' . strtotime("-6 months", $curdate) . ' AND sn.starttime between ' . strtotime("-6 months", $curdate) . ' AND ' . $curdate);
                                                                            } else if ($tempNewsletterID == -7) {
                                                                                // Filter for last year
                                                                                array_push($conditions, 'seo.opentime >= ' . strtotime("-1 year", $curdate) . ' AND sn.starttime between ' . strtotime("-1 year", $curdate) . ' AND ' . $curdate);
                                                                            }
                                                                        } else {
                                                                            //fix for when there are multiple "opened" a campaign rules to use "OR" instead of "AND"
                                                                            //otherwise we'll end up with a query condition like "(newsletter.id=1 AND newsletter.id=2)"
                                                                            if (!empty($rules[($k+1)])) {
                                                                                $nextRule = $rules[($k+1)];
                                                                                if ($nextRule['rules']['ruleName'] == 'campaign' && $nextRule['rules']['ruleOperator'] == 'equalto') {
                                                                                    $eachRule['connector'] = 'OR';
                                                                                }
                                                                            }
                                                                            array_push($conditions, 'sn.newsletterid = ' . $tempNewsletterID);
                                    }
                                } else {
                                                                        // If is an exclusion filter, we need a subquery to get opened emails that we will not include on the result
                                                                        $subquery = 'SELECT subscriberid FROM [|PREFIX|]stats_emailopens AS seo'
                                    . ', [|PREFIX|]stats_newsletters AS sn'
                                    . ' WHERE sn.statid=seo.statid';
                                                                        
                                                                        $tempNewsletterID = intval($eachRule['rules']['ruleValues'][0]);
                                                                        
                                                                        // Filter for custom campaign
                                                                        if ($tempNewsletterID >= 0) {
                                                                            $subquery .= ' AND sn.newsletterid=' . $tempNewsletterID;    
                                                                        } else {
                                                                            // Advanced filter
                                                                            $curdate = time();
                                                                            
                                                                            if ($tempNewsletterID == -2) {
                                                                                $laststatid = $this->_GetLastStatId($listids);
                                                                                $subquery .= ' AND sn.statid = ' . $laststatid;                                                                                
                                                                            } else if ($tempNewsletterID == -3) {
                                                                                // Filter for last week stats
                                                                                $subquery .= ' AND (seo.opentime >= ' . strtotime("-1 week", $curdate) . ' AND sn.starttime between ' . strtotime("-1 week", $curdate) . ' AND ' . $curdate . ')';
                                                                            } else if ($tempNewsletterID == -4) {
                                                                                // Filter for last month
                                                                                $subquery .= ' AND (seo.opentime >= ' . strtotime("-1 month", $curdate) . ' AND sn.starttime between ' . strtotime("-1 month", $curdate) . ' AND ' . $curdate . ')';
                                                                            } else if ($tempNewsletterID == -5) {
                                                                                // Filter for last 3 months
                                                                                $subquery .= ' AND (seo.opentime >= ' . strtotime("-3 months", $curdate) . ' AND sn.starttime between ' . strtotime("-3 months", $curdate) . ' AND ' . $curdate . ')';
                                                                            } else if ($tempNewsletterID == -6) {
                                                                                // Filter for last 6 months
                                                                                $subquery .= ' AND (seo.opentime >= ' . strtotime("-6 months", $curdate) . ' AND sn.starttime between ' . strtotime("-6 months", $curdate) . ' AND ' . $curdate . ')';
                                                                            } else if ($tempNewsletterID == -7) {
                                                                                // Filter for last year
                                                                                $subquery .= ' AND (seo.opentime >= ' . strtotime("-1 year", $curdate) . ' AND sn.starttime between ' . strtotime("-1 year", $curdate) . ' AND ' . $curdate . ')';
                                                                            }
                                                                        }
                                                                        
                                                                        // Attach advanced filter to new subfilter
                                    if ($this->_subqueryCapable()) {
                                        array_push($conditions, 'subscribers.subscriberid NOT IN (' . $subquery . ')');
                                    } else {
                                        $records = array('0');
                                        $rs = $this->Db->Query($subquery);
                                        while ($row = $this->Db->Fetch($rs)) {
                                            $records[] = (int)$row['subscriberid'];
                                        }

                                        $this->Db->FreeResult($rs);
                                        array_push($conditions, 'subscribers.subscriberid NOT IN ('. implode(',', $records) .')');
                                    }
                                }
                                break;

 

/* Segment by opened campaign #2*/



Before
function _GenerateSegmentRuleQuery_Customfield($eachRule)


/* Segment by opened campaign #3*/
        /**
         * Get the id of the last campaign sent, related to the given lists
         *
         * @param type $listids
         * @return Last stat id or -1 if any campaign has been sent
         */
        function _GetLastStatId($listids) {
            if (empty($listids) || count($listids) == 0) {
                return -1;
            }
            
            $query = "SELECT snl.statid AS laststatid FROM " .
                    SENDSTUDIO_TABLEPREFIX . "stats_newsletter_lists snl, " .
                    SENDSTUDIO_TABLEPREFIX . "stats_newsletters sn WHERE snl.statid=sn.statid AND snl.listid IN (" . implode(',', $listids) . ")
                        AND sn.trackopens=1 order by laststatid desc LIMIT 1";

            $result = $this->Db->Query($query);
            $laststatid = $this->Db->FetchOne($result, 'laststatid');
            if (empty($laststatid)) {
                // Any campaign has been sent
                $laststatid = -1;
            }
            return $laststatid;
        }
        /* End Segment by opened campaign #3*/

        
        
        
admin/com/language/default/language.php
/* Segment by opened campaign #4*/
define('LNG_FilterLastNewsletter','Last campaign');
define('LNG_FilterLastWeekNewsletters','Last week campaings');
define('LNG_FilterLastMonthNewsletters','Last month campaings');
define('LNG_FilterLast3MonthNewsletters','Last 3 months campaings');
define('LNG_FilterLast6MonthNewsletters','Last 6 month campaings');
define('LNG_FilterLastYearNewsletters','Last year campaings');
/* End Segment by opened campaign #4*/




admin/functions/api/stats.php
Method: GetOpens (line aprox #1390)

Insert after
if (!is_array($statids)) {
            $statids = array($statids);
}

CODE:

// Addon Partitions Change #1
$query = "SELECT min(starttime) as st FROM " . SENDSTUDIO_TABLEPREFIX . "stats_newsletters WHERE statid in (" . implode(',', $statids) . ")";
$result = $this->Db->Query($query);
$firstsendtime = $this->Db->FetchOne($result, 'st');                        
$calendar_restrictions .= ' AND o.opentime >= ' . $firstsendtime;
// End Addon Partitions Change #1

¿Tiene más preguntas? Enviar una solicitud

0 Comentarios

El artículo está cerrado para comentarios.
Tecnología de Zendesk