mysqlinnodbinsert-statement

will this INSERT profile work with my Mysql InnoDB oriented DB?


I am wondering on the following subject (regarding INSERT on Mysql - InnoDB tables).

I have a system that does among other system 2 things

  1. promote a newsletter to users
  2. mass emails to a certain type of users

now, the users will grow to 4k, and supposedly to 40k

When I'm mass emailing, I'm doing an INSERT for each user id in a WHILE (which loops through all the users).

I have 2 questions :

  1. Is this the best way to do it ?
  2. When users reach 40K (40.000 users), will the mysql-server sustain this ?

thanks,

this is the piece of code i'm using

    $query_write_mass = "SELECT id FROM mya_users ORDER by artist_real_address ASC";
    $result_write_mass = $db->prepare($query_write_mass);
    $result_write_mass->execute();
    while ( list($receiver_id) = $result_write_mass->fetch(PDO::FETCH_BOTH) ) { 

       $stmt = $db->prepare
         ("INSERT INTO inbox(folder_id, sender_id, sender_type, receiver_id, 
           receiver_type, title, message_body, time, date, flag, spam) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            $stmt->bindValue(1, 0, PDO::PARAM_INT);
            $stmt->bindValue(2, 0, PDO::PARAM_INT);
            $stmt->bindValue(3, 'x', PDO::PARAM_STR);
            $stmt->bindValue(4, $receiver_id, PDO::PARAM_INT);
            $stmt->bindValue(5, $receiver_type, PDO::PARAM_STR);
            $stmt->bindValue(6, $_POST['title'], PDO::PARAM_STR);
            $stmt->bindValue(7, $_POST['body'], PDO::PARAM_STR);
            $stmt->bindValue(8, date("G:i:s"), PDO::PARAM_STR);
            $stmt->bindValue(9, date("Y-m-d"), PDO::PARAM_STR);
            $stmt->bindValue(10, 'n', PDO::PARAM_STR);
            $stmt->bindValue(11, '', PDO::PARAM_STR);                                                                                                                                                                                                   

            $stmt->execute();   
     }

This is for the mass emailing for my internal email system.

UPDATE

You can find a good answer to this question in my other post (resolved) @

multiple INSERTS and keeping PDO prepared statement security


Solution

  • 1) No. If you can, insert several rows at a time :

    INSERT INTO t VALUES (1, 'something'), (2, 'something else')...
    

    And if you insert a similar record for all users, an even better approach would be:

    INSERT INTO t
    SELECT 
        "something to insert for all users into t's column 1",
        "something to insert for all users into t's column 2",
        user.field1,
        user.field2
    FROM user
    -- WHERE some_condition_on_user_table
    

    2) Yes, unless you proceed with while loops (but even then, it may just work too)