Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    6

    Unanswered: initialize large simple table

    This is likely a very easy question, but I'm a pretty raw beginner. I'm using C and mySQL to create a large (4.6 billion) entry table with just two columns. My create table statement is as follows:

    CREATE TABLE test1(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, count INT UNSIGNED NOT NULL, PRIMARY KEY (id))

    I want to initialize the 'count' column to zero and have 'id' auto increment. I've read there is a way to take a small table and double it in a single command. What I've done so far is create 137 entries in a C loop:

    INSERT INTO test1 (count) VALUES(0)

    which takes a surprising amount of time (~10 seconds and I can hear the disk drive going crazy with seeking). Can someone help with the single command that doubles the number of entries? I believe it is something like the following:

    INSERT INTO test1 SELECT NULL, xxx, xxx FROM test1

    but I can't figure it out completely from the web searching I've done. Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something like this, perhaps?
    Code:
    INSERT INTO test1 (`count`)
    SELECT `count`
    FROM   test`
    Basically we take the results of the SELECT statement and chuck it back in the table.

    P.S. count is a reserved word, so should not be used for object/column names.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2015
    Posts
    6
    Thanks for your reply. I've partially got what I want (and changed column 'count' to 'knt'). How do I make the 'id' field increment without any gaps in the numbering?

    Code:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 69
    Server version: 5.5.41-0ubuntu0.12.04.1 (Ubuntu)
    
    --------------
    DROP TABLE IF EXISTS test1
    --------------
    
    Query OK, 0 rows affected (0.06 sec)
    
    --------------
    CREATE TABLE test1(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT primary key, knt INT UNSIGNED NOT NULL)
    --------------
    
    Query OK, 0 rows affected (0.10 sec)
    
    --------------
    INSERT INTO test1 (knt) VALUES(0)
    --------------
    
    Query OK, 1 row affected (0.04 sec)
    
    --------------
    select * from test1
    --------------
    
    +----+-----+
    | id | knt |
    +----+-----+
    |  1 |   0 |
    +----+-----+
    1 row in set (0.00 sec)
    
    --------------
    insert into test1 (knt) 
    select knt 
    from test1
    --------------
    
    Query OK, 1 row affected (0.04 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    --------------
    select * from test1
    --------------
    
    +----+-----+
    | id | knt |
    +----+-----+
    |  1 |   0 |
    |  2 |   0 |
    +----+-----+
    2 rows in set (0.00 sec)
    
    --------------
    insert into test1 (knt) 
    select knt 
    from test1
    --------------
    
    Query OK, 2 rows affected (0.04 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    --------------
    select * from test1
    --------------
    
    +----+-----+
    | id | knt |
    +----+-----+
    |  1 |   0 |
    |  2 |   0 |
    |  3 |   0 |
    |  4 |   0 |
    +----+-----+
    4 rows in set (0.00 sec)
    
    --------------
    insert into test1 (knt) 
    select knt 
    from test1
    --------------
    
    Query OK, 4 rows affected (0.04 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    --------------
    select * from test1
    --------------
    
    +----+-----+
    | id | knt |
    +----+-----+
    |  1 |   0 |
    |  2 |   0 |
    |  3 |   0 |
    |  4 |   0 |
    |  6 |   0 |
    |  7 |   0 |
    |  8 |   0 |
    |  9 |   0 |
    +----+-----+
    8 rows in set (0.00 sec)
    
    --------------
    insert into test1 (knt) 
    select knt 
    from test1
    --------------
    
    Query OK, 8 rows affected (0.04 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    
    --------------
    select * from test1
    --------------
    
    +----+-----+
    | id | knt |
    +----+-----+
    |  1 |   0 |
    |  2 |   0 |
    |  3 |   0 |
    |  4 |   0 |
    |  6 |   0 |
    |  7 |   0 |
    |  8 |   0 |
    |  9 |   0 |
    | 13 |   0 |
    | 14 |   0 |
    | 15 |   0 |
    | 16 |   0 |
    | 17 |   0 |
    | 18 |   0 |
    | 19 |   0 |
    | 20 |   0 |
    +----+-----+
    16 rows in set (0.00 sec)
    
    mysql> quit

  4. #4
    Join Date
    Jan 2015
    Posts
    6
    I discovered that using ENGINE=MYISAM produced sequential values for 'id' without any gaps in the numbering. Since I specified that column as auto-increment, why would INNODB not produce sequential numbers but MYISAM does?

    Also, I noticed a huge difference in disk activity. With INNODB there was a great deal of disk activity with each INSERT command, but almost none using MYISAM. What would that be?

  5. #5
    Join Date
    Jan 2015
    Posts
    6
    Somewhere I read that when using InnoDB, setting the autoinc lock mode might solve the problem. But, when I try to set it on the command line, I get an error. What am I doing wrong?

    $ mysql -u root -p --innodb_autoinc_lock_mode=0
    mysql: unknown variable 'innodb_autoinc_lock_mode=0'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •