How to optimize multiple insert into wordpress database

To optimize the query, instead of use $wpdb->insert is possible to use $wpdb->query and use a single query that insert all the items.

In fact, according to MySQL documentation:

If you are inserting many rows from the same client at the same time,
use INSERT statements with multiple VALUES lists to insert several
rows at a time. This is considerably faster (many times faster in some
cases) than using separate single-row INSERT statements

So, if you have an array $all_items where every array item is an array having ‘address’, ‘kod’ and ‘number’ items, something like:

$all_items = array(

  array( 'address' => 'test', 'kod' => '096-024', 'number' => '4' ), 

  array( 'address' => 'test2', 'kod' => '096-025', 'number' => '5' ), 

  array( 'address' => 'test3', 'kod' => '096-026', 'number' => '6' ),  

);

then you can

$q = "INSERT INTO $ow_table_adres (address, kod, number) VALUES ";

foreach ( $all_items as $an_item ) {
  $q .= $wpdb->prepare(
    "(%s, %s, %d),",
    $an_item['address'], $an_item['kod'], $an_item['number']
  );
}

$q = rtrim( $q, ',' ) . ';';

$wpdb->query( $q );

Related Posts: