Monthly Archives: September 2013

Mail 550 filter

Posted by on September 25, 2013 No comments

If you make a project with huge amount of email notification, you MUST control number of 550 reply from mail servers. Because if you skip this step, and continue sending to deleted mail boxes, big mail providers such as gmail.com, mail.ru, mail.ua, etc. will ban you domain at 0.5 – 1% “user unknown” reply.
So mail.log parsing is only solution.
In our project we add bad email addresses to database table (we use postgress)

1. make database replace rule, if email already added, (email is primary key) it is fastest way to prevent errors on INSERT duplicate email addresses.
Read more »

Some mysql query optimization DISTINCT,GROUP BY, etc :)

Posted by on September 5, 2013 No comments

Our previous developer use standard SQL guide to create query for selection “top 50 referrers”

SELECT referrer  FROM referrers_log WHERE (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123  GROUP BY referrer ORDER BY SUM(views_count) DESC LIMIT 50;

50 rows in set (26.31 sec)

It significantly loads our database;

Rewrite query for using temporary table with distinct referrer:

DROP TEMPORARY TABLE REF;
CREATE TEMPORARY TABLE REF AS (SELECT DISTINCT referrer FROM referrers_log WHERE (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123);
SELECT REF.referrer,SUM(views_count) FROM referrers_log,REF
WHERE referrers_log.referrer=REF.referrer
AND (create_date = curdate() OR create_date=curdate()-1) AND site_id = 123
GROUP BY REF.referrer ORDER BY SUM(views_count) DESC LIMIT 50;

50 rows in set (2.48 sec)

I feel happy 🙂 🙂 🙂