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!
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?