Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider a PHP application which inserts about 200-800 records in a loop via prepared queries and parametrized values. The PHP application is on Amazon Web Services, and the database server is in Amazon Relational Database Service.

$sql = "INSERT INTO phone_list(added, name, address, city, state, zip, country, phone, email, website, full_output, source, client, campaign, search_term, search_location, is_valid) ";
$sql.= "VALUES(now(),:name, :address, :city, :state, :zip, :country, :phone, :email, :website, :full_output, :source, :client, :campaign, :search_term, :search_location, :is_valid)";

$stmt = $pdo->prepare($sql);

foreach ( $records as $r ) {
    $stmt->bindValue(':name', $r['name']);
    // Whole bunch of additional bindValue()s
}

Notice that the INSERT starts with column added which is using MySQL's NOW() function. Since the database server is being accessed over the network, I am inclined to prefer MySQL's NOW() function. However, I don't know if MySQL will cache the result of the call to the system clock, or if it will hit the clock, then format, then put the value in, once for each INSERT. If that is the case, then would it be preferable to set a variable once at the beginning of the PHP script $now = date('c') and then reuse that variable for each row?

Note that this application does not need precision in the 'added' datetimes, even an error of a day would be fine. I stress that this will be running in a very tight loop (approaching 1000 rows per run) so each little bit matters. Neither the application server nor the database server seem starved of either CPU nor memory. The table in question is an InnoDB table.

Edit: I have read similar questions such as this one however they have different considerations that I do. For instance, I am running a remote database server (not on the same server as the application) and also I am concerned about the heavy loop.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.