CODEWITHSUNDEEP
phpmysqllimit
john
john

Reputation: 1330

Limiting multiple results from a mysql query

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.

Upvotes: 0

Views: 211

Answers (1)

srini.venigalla
srini.venigalla

Reputation: 5145

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.

Upvotes: 1

Related Questions