Skip to main content

Return to Answer

added 377 characters in body
Source Link
Jason S
Jason S
  • 190.6k
  • 176
  • 637
  • 1k

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`; Query OK, 32 rows affected (0.03 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`; Query OK, 64 rows affected (0.03 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`; Query OK, 128 rows affected (0.05 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`; Query OK, 256 rows affected (0.03 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`; Query OK, 512 rows affected (0.11 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700; Query OK, 700 rows affected (0.16 sec) Records: 700 Duplicates: 0 Warnings: 0

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).

edit: You could use a MEMORY storage engine to prevent this from actually being a drain on the "real" database. I wonder if someone has developed a "NUMBERS" virtual storage engine to instantiate virtual storage to create sequences such as this. (alas, nonportable outside MySQL)

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`; Query OK, 32 rows affected (0.03 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`; Query OK, 64 rows affected (0.03 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`; Query OK, 128 rows affected (0.05 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`; Query OK, 256 rows affected (0.03 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`; Query OK, 512 rows affected (0.11 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700; Query OK, 700 rows affected (0.16 sec) Records: 700 Duplicates: 0 Warnings: 0

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`; Query OK, 32 rows affected (0.03 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`; Query OK, 64 rows affected (0.03 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`; Query OK, 128 rows affected (0.05 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`; Query OK, 256 rows affected (0.03 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`; Query OK, 512 rows affected (0.11 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700; Query OK, 700 rows affected (0.16 sec) Records: 700 Duplicates: 0 Warnings: 0

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).

edit: You could use a MEMORY storage engine to prevent this from actually being a drain on the "real" database. I wonder if someone has developed a "NUMBERS" virtual storage engine to instantiate virtual storage to create sequences such as this. (alas, nonportable outside MySQL)

Source Link
Jason S
Jason S
  • 190.6k
  • 176
  • 637
  • 1k

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`)); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`; Query OK, 32 rows affected (0.03 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`; Query OK, 64 rows affected (0.03 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`; Query OK, 128 rows affected (0.05 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`; Query OK, 256 rows affected (0.03 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`; Query OK, 512 rows affected (0.11 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700; Query OK, 700 rows affected (0.16 sec) Records: 700 Duplicates: 0 Warnings: 0

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).