0

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?

0

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');
  • Thank you very much! In official documentation is this: Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead. Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively. So it confused me and I didnt try to use db_query().... – Anchor_Chisel Sep 13 '17 at 17:05
  • Yeah that's a bit of a shame. The docs mean well but the information is wrong – Clive Sep 13 '17 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.