Limiting multiple results from a mysql query

Question

We have a sql query that pushes contacts into the queue for our mailing system. One thing we had realized was each of our mailing ip's have their own limits. Meaning that instead of mailing say 2 or 3 email a minute for each ip to all destination domains, we can send 2 or 3 per ip per domain. We have a working query that cross checks our blacklist as well as our tracking system so we don't remail to users who have performed actions.

$query = "SELECT `email`,`template`,`id` FROM `q_main` WHERE 
            `email` NOT IN (SELECT `email` FROM `c_blacklist` WHERE `email` = `q_main`.`email`) AND 
            `email` NOT IN (SELECT `s_log`.`email` FROM `s_log`,`t_analytics` WHERE `t_analytics`.`key` = `s_log`.`key` AND `q_main`.`template` = `t_analytics`.`campaign_id`) LIMIT ".$num_nodes."";

The above query works perfect. It simply fetches the next set of emails that match the query results, regardless of destination domain. The limit is set by multiplying the amount of ip addresses by a max per minute for each ip (usually 2 or 3 a minute).

To collect all the available destination domains in the queue, we can run the query below. This fetches all the destination domains that are in the queue as well as the count.

$query = "SELECT SUBSTRING_INDEX( email, '@', -1 ) AS dd, COUNT( * ) AS cc FROM q_main GROUP BY SUBSTRING_INDEX( email, '@', -1 ) ORDER BY cc DESC";
$result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
        $send_domains[] = $row['dd'];
    }

Below is our test query which essentially does nothing. It queries the same thing as the first query, only now it implodes the array of destination domains. What it's NOT doing is limiting the results of each destination domain which is what we're trying to accomplish.

$query = "SELECT `email`,`template`,`id` FROM `q_main` WHERE 
            `email` NOT IN (SELECT `email` FROM `c_blacklist` WHERE `email` = `q_main`.`email`) AND 
            `email` NOT IN (SELECT `s_log`.`email` FROM `s_log`,`t_analytics` WHERE `t_analytics`.`key` = `s_log`.`key` AND `q_main`.`template` = `t_analytics`.`campaign_id`) AND 
            `email` LIKE '%".implode("' OR `email` LIKE '%",$send_domains)."' ";

To sum it all up, what we're trying to do is select and limit records based on each destination domain. How can we set a limit for each destination domain without limiting the overall query like we did in the first example? We did try looping the query through each destination domain, but it did not perform well at all.

Also for those who are curious, we are trying to do this to implement a scoring system for our mailing ip's. This way we'll be able to limit or skip destination domains when softbounces or negative feedback comes through on a particular ip.

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Jul 3, 2012 at 19:06

Ben · Accepted Answer · 2012-07-03 21:20:15Z

1

Maintain the limits for each domain in a separate table and join that table to your query, so that the limit is available for each domain. Then, number your rows for each domain. In the where condition, exclude the rows whose rownumber > domain limit.

That said, mysql does not have row number function. It is done using a row variable, here is the sample.

edited Jul 3, 2012 at 21:20

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Jul 3, 2012 at 19:07

srini.venigalla

5,1551 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Limiting multiple results from a mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related