From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE
or PRIMARY
; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id
column, removing it and making domain
the primary key would help performance.
Changing the collation should help since ascii
is much simpler than utf8
, but you might want to use ascii_general_ci
instead of ascii_bin
since domain names are case-insensitive.
One other way to do the queries would be to get the number of rows, INSERT DELAYED
, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.
PROBLEM
You need to insert nothing ?
SOLUTION
Then, insert nothing at all ... literally !!!
One of the following will work
INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
SAMPLE DATA
use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;
SAMPLE DATA LOADED
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE test2
-> (id int not null auto_increment primary key,
-> test int default 0);
Query OK, 0 rows affected (0.34 sec)
mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql>
QUERIES EXECUTED
If id
and test
are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2
besides id
and test
, name the test
column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0
will become 0
.
mysql> SELECT * FROM test2;
Empty set (0.00 sec)
mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
+----+------+
9 rows in set (0.00 sec)
mysql>
GIVE IT A TRY !!!
CAVEAT #1 : I did this in Windows 8.1 from the command line
mysql> select * from information_schema.global_variables
-> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64 |
| VERSION_COMPILE_OS | Win64 |
| VERSION | 5.6.15 |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)
mysql>
CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.
Best Answer
An "Implicit Default Value" seems to be in reference to the type; the "Explicit Default Value" or simply "Default Value" seems to be in reference to the column's
DEFAULT
,Also from the docs,
you can see this documented here in "11.7 Data Type Default Values"
Running through a quick check,
Whipe it and set strict mode,
Retry,
You'll see now only one row in the table which is a result of the goofy syntax here that is still permitted with "strict mode".