insert then update inside loop

Question

Hi Guys,

I have a php script, I use it to be run in as cron job.

When this script running it takes about 13 minutes on 16000 user records.

Please advice me to make this script running with the best performance.

I need to know that if there is any problem if i put update then insert inside a loop, or insert then update inside a loop.

$db_conn = getDbObject('my_db');

$now_timestamp = time();

$message_text = getMessage('notify');

$users_sql = "SELECT * FROM users_tbl WHERE status = 'sub' and expire_timestamp < '{$now_timestamp}' ";

$users_result = mysql_query($users_sql,$db_conn);

while($user_row = mysql_fetch_assoc($users_result)){

    $update_user_sql = "UPDATE users_tbl SET status = 'pending' WHERE user_id = '{$user_row['user_id']}' ";

    mysql_query($insert_message_sql,$db_conn);

    $insert_message_sql = "INSERT INTO 
                                messages_tbl 
                                        (
                                            message_id ,
                                            message_text ,
                                            user_id ,
                                            status                          
                                        )
                                    VALUES 
                                        (
                                            NULL , 
                                            '{$message_text}', 
                                            '{$user_row['user_id']}',  
                                            '0',                                            
                                        )";


    mysql_query($insert_message_sql,$db_conn);

}

mysql_close($db_conn);

Answer 1

I need to know that if there is any problem if i put update then insert inside a loop, or insert then update inside a loop.

Sure this is a problem.

You should get rid of the loop and make your operations set-based:

INSERT
INTO    messages_tbl (message_id, message_text, user_id, status)
SELECT  NULL, '{$message_text}', user_id, '0'
FROM    users_tbl
WHERE   status = 'sub' 
        AND expire_timestamp < '{$now_timestamp}'

UPDATE  users_tbl
SET     status = 'pending'
WHERE   status = 'sub' and expire_timestamp < '{$now_timestamp}'