How to optimize a query with MySQL multi-query?

Question

I need to optimize a script for high performance so the question is how can I add MySQL multi-query to this code?

foreach($multiContent as $htmlContent) {
    $email = urldecode($email['1']);
    $user = $user['1'];

    //store in db
    $db->query("UPDATE eba_users 
                   SET mail = '$email' 
                 WHERE username = '$user'");
    //echo "email is $email and user is $user\n";
  }

  //close if ($array_counter % 200
  unset($array_counter);
  unset($data);
}

@Phil , In the foreach statement there is a loop of about 200 "usernames" and emails so instead to run 200 SQL statements I would like to run only one bigger statement.. I think can be done with multi-query but I'm not very experienced with mysql .. Hope this would improve the performance/latency — Michael, Aug 09 '11 at 00:47
What is "multi-query"? If you mean executing multiple statements in the one call, this cannot be done with any PHP MySQL library. You are better to use prepared statements as mentioned in Mike's answer below — Phil, Aug 09 '11 at 00:50
I found some documentation on php site http://php.net/manual/en/mysqli.multi-query.php — Michael, Aug 09 '11 at 01:03
If your query does not differ, you're still better ahead to use prepared statements. From the PDO manual: "For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute()" — Mike, Aug 09 '11 at 01:09
@Michael I stand corrected. I don't use the mysqli library and hadn't seen that before. I still agree with Mike though. — Phil, Aug 09 '11 at 01:12
Michael, the multi-query option won't optimize your query in this situation since all it does is send the queries to the server simultaneously. The multi-query method is advantageous when you have multiple tables that you're querying concurrently without any overlap between the tables. In your case, the index read/writes would render multi-query the same performance as your current method. @Mike has the best solution. — Eric Caron, Aug 09 '11 at 01:48

score 3 · Accepted Answer · edited May 23 '17 at 12:04

If you're using mysqli or PDO already, you should be using prepared statements for your queries since they are supported. This will also have a slight increase in performance since the entire query doesn't need to be sent again to the DB server. However the biggest advantage is the increased security that prepared statements provide.

Other than this, try adding an index on username to speed this query up if you haven't already.

Edit: If you want to do it all in one query, as you seem to suggest, you could also use ON DUPLICATE KEY UPDATE as mentioned as an answer to this question:

INSERT INTO eba_users (`username`, `mail`) 
VALUES 
    ('username1','$email1'),
    ('username2','$email2'),
    ('username3','$email3'),
    ('username4','$email4'),
    ....
    ('usernameN','$emailN'),
ON DUPLICATE KEY UPDATE `mail`=VALUES(mail);

However this may not be as fast as using prepared statements with a regular UPDATE.

Edit2: As requested, here is probably a close approximation of what you should be doing to bind the parameters in mysqli:

if ($stmt = $mysqli->prepare("UPDATE eba_users SET mail= ? WHERE username= ?")) {

    /* loop through array of users */
    foreach ($array as $username => $newemail) {

        /* bind parameters for markers */
        $stmt->bind_param("ss", $newemail, $username);

        /* execute query */
        $stmt->execute();
    }
}

Of course this doesn't provide any sort of error messages in case this fails. For that, you can look into mysqli::error

Can you convert my query into prepared statement :) ? I keep trying to make it work but I'm getting some bind() errors . I have | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(1) | NO | PRI | NULL | auto_increment | | username | varchar(208) | YES | UNI | NULL | | | status | int(1) | YES | | 0 | | | mail | varchar(65) | YES | | NULL | | — Michael, Aug 09 '11 at 01:36

How to optimize a query with MySQL multi-query?

1 Answers1