MYSQL SELECT WITH PAUSE

Question

We are making an php emailer which works perfect.

Selecting all the users from a database and send them emails are good to go.

But, since were have a huge amount of emails that has to be send, we would like start and pause the transactions of emails with [ 1000 ] to not overload the server.

Example:

SELECT: 1000;

PAUSE MYSQL

SELECT ANOTHER 1000;

PAUSE MYSQL

ETC.

I read about the START TRANSACTION, COMMIT & ROLLBACK functions, and I think I implemented this right..

Can someone help me to include a pause of 100 seconds before ROLLBACK the transaction?

I don't know what to do..

What i got until now [prefixed code]..

$max=1000;

$send=0;

$rollback=false;

mysql_query('START TRANSACTION;');

$query = mysql_query("SELECT DISTINCT mail_id, customers_email_address newsletters WHERE ORDER BY mail_id ASC");

while($result=mysql_fetch_array($query){

if( $rollback == true ){

$rollback = false;

mysql_query("ROLLBACK;");

}

[------script to send the emails-----]

$send++;

if( $max == $send ){

mysql_query("COMMIT;");

$rollback = true;

}

}

Cheers Jay

You are not modifying any information in the database so using a transaction doesn't make sense. Do you plan to keep track of which emails / to who were sent in the database? — jeroen, Jan 08 '13 at 15:45
What jeroen said. Also, stop using `mysql_` functions in new code! — Kermit, Jan 08 '13 at 15:48
Be aware that the `mysql_xxx()` functions in PHP are obsolete, insecure and deprecated. You should switch to either the `mysqli_xx()` functions or the PDO library as soon as possible. See also http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — SDC, Jan 08 '13 at 15:51
Code formatting simply requires four or more spaces. Please don't use quoting like you have, it makes the code much harder to read. — tadman, Jan 08 '13 at 16:52

score 3 · Answer 1 · answered Jan 08 '13 at 15:46

3

There is no need for transactions here at all - you're not updating anything. In fact, the overhead of transactions is entirely pointless here, so I'd advise you take that out.

You could simply (in theory, you can write the code for this)

Select the first 1000 rows from the database: SELECT ... LIMIT 0, 1000
Increment your offset by 1000
Select the next 1000 rows: SELECT ... LIMIT 1000, 1000
Rinse and repeat, until you get less than 1000 rows back from your query.

Please note that in order for that method to work, you'll want to ORDER BY the primary key in ASC order or something, to be sure you don't get the same row twice.

answered Jan 08 '13 at 15:46

Colin M

13,010
3
38
58

Thnx, this helped me out ! – Jayr Jan 08 '13 at 15:50
And if delays are needed, put a `sleep()` in the PHP section. – Mr. Llama Jan 08 '13 at 15:51
yes, i know, but is there not an sleep/delay function in mysql? – Jayr Jan 08 '13 at 15:56
@JayRuben no. mySQL is not a programming language, it is a query language. It would be counter-productive for any query to simply stop in the middle for an arbitrary period of time, tying up resources and potentially locking tables from being used in other queries/processes. – Sammitch Jan 08 '13 at 16:03
It's not MySQL's job to take a time-out for you. That's an application-level concern. – tadman Jan 08 '13 at 16:53

score 1 · Answer 2 · answered Jan 08 '13 at 15:45

1

all you need is to schedule your sender script with cron for example and sending some amount of emails. (in sql use LIMIT).

it will send than N emails every M minutes and server will be happy ;)

answered Jan 08 '13 at 15:45

macino

457
4
12

score 0 · Answer 3 · answered Jan 08 '13 at 15:55

Few optios like below:

1) You can implement Cronjob.

2) There is a opensource small application of php as PHPList which can be integrated in few seconds. (i already use this one, so)

3) 3rd option, you can use sleep function of php. (i am not sure about this)

MYSQL SELECT WITH PAUSE

3 Answers3