Execute complex SQL queries

Question

I need to execute these SQL instructions.

CREATE TABLE superstatistics_counter LIKE node_counter;
INSERT superstatistics_counter SELECT * FROM node_counter;

When I run the query, it runs. I expect the node_counter table to get a big table with about 100000 rows. This is what I have tried:

1. With hook_schema() I have created the superstatistics_counter table

2. With this code I tried to copy data from the node_counter table

   $sql = "SELECT * FROM `node_counter`";
   $data = db_query($sql)->fetchAll();
   foreach ($data as $dataObject) {
     $dataArray = array($dataObject->nid, $dataObject->totalcount, $dataObject->daycount, $dataObject->timestamp);
     $query = \Drupal::database()->insert('superstatistics_counter');
     $query->fields([
       'nid',
       'totalcount',
       'daycount',
       'timestamp'
     ]);
     $query->values($dataArray);
     $query->execute();
   }
   

It caused the following error:

Maximum execution time of 120 seconds exceeded

So it is very slow.

When I executed it in phpMyAdmin, it output the following.

MySQL returned an empty result set (i.e. zero rows). (Query took 0.1401 seconds.)
CREATE TABLE superstatistics_counter LIKE node_counter

100000 rows inserted. (Query took 0.9567 seconds.)
INSERT superstatistics_counter SELECT * FROM node_counter

Is there a way to just execute this query in Drupal, without using db_query() and \Drupal::database()->insert()? Can it be done on MySQL side?

Answer 1

The db_query function isn't just for select queries, it will execute anything.

Just use that instead of the loop, the performance will be miles better:

db_query('INSERT INTO superstatistics_counter SELECT * FROM node_counter');