Hi All,

On this occasion, I'd like to talk to you on how to remove un-used records (that is caused by autoresponders) from the queues table that will never be used by the application. The advantage of keeping the queues table clean is obvious. It will speed up query time for and reduce CPU load when CRON run.

This article will only apply with the following curcumtances:

  • You have autoresponders that will only be sent out to confirmed contacts
  • All your subscription forms are double-opt-in

The code that I'm going to show you is based on IEM 5.5.4, I have not test this code with any other versions (but it should be working for IEM 5 or above, only the line numbers may be different).

As always, please make sure you make backups first, and if possible run any queries in the "Staging Server" before executing them in your live server.

NOT ADDING RECORD TO QUEUES TABLE

You can prevent autoresponder record for un-confirmed subscribers from being added in to the queues table at the subscription time.

To do this, you need to open up form.php (around line 471): 

$subscriber_id = $subscriberapi->AddToList($email, $listid, true, true);

You can change the third parameter of "AddToList" function call to FALSE to not add your subscribers to the autoresponder. 

$subscriber_id = $subscriberapi->AddToList($email, $listid, FALSE, true);

Please be careful doing the above modification though. This is because the autoresponder will only be scheduled for the subscribers if:

  • The contact is added in manually
  • When a contact confirm his subscription (so web-form must use double-opt in)

CLEANING UP THE QUEUE TABLE

To clean up the queue table from autoresponders record that will never be used anyway (because the contact have not yet confirmed), you can use the following query string:

DELETE FROM queues
WHERE  recipient IN (
         SELECT subscriberid FROM (
           SELECT subscriberid
           FROM list_subscribers
           WHERE confirmed = '0'
         ) AS x
       )
       AND queuetype = 'autoresponder'