########## 7.2.12 [root@cluster-repo mysql-cluster-com-7_2_12]# bin/mysql -u root -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30-ndb-7.2.12-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> mysql> drop table keyvalue; ERROR 1051 (42S02): Unknown table 'keyvalue' mysql> CREATE TABLE `test`.`keyvalue` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `id_non_key` bigint NOT NULL, -> `name` varchar(40), -> PRIMARY KEY (`id`) -> ) ENGINE=NDB; Query OK, 0 rows affected (0.59 sec) mysql> set @id:=0; Query OK, 0 rows affected (0.00 sec) mysql> set @id_:=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into `keyvalue` values (@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into `keyvalue`(`id`,`name`) -> select @id:=@id+1,@id_:=@id_+1,md5(rand()*1000000) from -> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9, -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit -> 500000; ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into `keyvalue`(`id`,`id_non_key`,`name`) -> select @id:=@id+1,@id_:=@id_+1,md5(rand()*1000000) from -> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9, -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit -> 500000; Query OK, 500000 rows affected (3 min 44.69 sec) Records: 500000 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 23; +----+------------+----------------------------------+ | id | id_non_key | name | +----+------------+----------------------------------+ | 23 | 23 | 67f062a4eb84d5726eabca02467518c1 | +----+------------+----------------------------------+ 1 row in set (0.42 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 23; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '23'; +----+------------+----------------------------------+ | id | id_non_key | name | +----+------------+----------------------------------+ | 23 | 23 | 67f062a4eb84d5726eabca02467518c1 | +----+------------+----------------------------------+ 1 row in set (0.42 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '23'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 233333; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 233333 | 233333 | 86fcd8db5183b60a76cc9b3407fe30dc | +--------+------------+----------------------------------+ 1 row in set (0.45 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 233333; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '233333'; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 233333 | 233333 | 86fcd8db5183b60a76cc9b3407fe30dc | +--------+------------+----------------------------------+ 1 row in set (0.42 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '233333'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------------------------------------------+ | version() | +-----------------------------------------------+ | 5.5.30-ndb-7.2.12-cluster-commercial-advanced | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> set query_cache_type=OFF; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 233333; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 233333 | 233333 | 86fcd8db5183b60a76cc9b3407fe30dc | +--------+------------+----------------------------------+ 1 row in set (0.42 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 233333; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '233333'; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 233333 | 233333 | 86fcd8db5183b60a76cc9b3407fe30dc | +--------+------------+----------------------------------+ 1 row in set (0.42 sec) mysql> mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '233333'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 500004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) ########## 7.2.10 [root@cluster-repo mysql-cluster-com-7_2_10]# bin/mysql -u root -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.29-ndb-7.2.10-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> mysql> drop table if exists keyvalue; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> CREATE TABLE `test`.`keyvalue` ( -> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -> `id_non_key` bigint NOT NULL, -> `name` varchar(40), -> PRIMARY KEY (`id`) -> ) ENGINE=NDB; Query OK, 0 rows affected (0.60 sec) mysql> set @id:=0; Query OK, 0 rows affected (0.00 sec) mysql> set @id_:=0; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into `keyvalue` values (@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)),(@id:=@id+1,@id_:=@id_+1,md5(rand()*1000000)); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into `keyvalue`(`id`,`id_non_key`,`name`) -> select @id:=@id+1,@id_:=@id_+1,md5(rand()*1000000) from -> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9, -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit -> 950000; Query OK, 950000 rows affected (10 min 15.22 sec) Records: 950000 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 23; +----+------------+----------------------------------+ | id | id_non_key | name | +----+------------+----------------------------------+ | 23 | 23 | 6ce1d3d049b04821a4a4ccad0c206445 | +----+------------+----------------------------------+ 1 row in set (0.90 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 23; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.03 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '23'; +----+------------+----------------------------------+ | id | id_non_key | name | +----+------------+----------------------------------+ | 23 | 23 | 6ce1d3d049b04821a4a4ccad0c206445 | +----+------------+----------------------------------+ 1 row in set (0.88 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '23'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 666666; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 666666 | 666666 | d52375b722117a9ca0dc071abe5aad57 | +--------+------------+----------------------------------+ 1 row in set (0.86 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 666666; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '666666'; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 666666 | 666666 | d52375b722117a9ca0dc071abe5aad57 | +--------+------------+----------------------------------+ 1 row in set (0.86 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '666666'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> set query_cache_type=OFF; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM keyvalue WHERE `id_non_key` = 666666; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 666666 | 666666 | d52375b722117a9ca0dc071abe5aad57 | +--------+------------+----------------------------------+ 1 row in set (0.90 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = 666666; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM keyvalue WHERE `id_non_key` = '666666'; +--------+------------+----------------------------------+ | id | id_non_key | name | +--------+------------+----------------------------------+ | 666666 | 666666 | d52375b722117a9ca0dc071abe5aad57 | +--------+------------+----------------------------------+ 1 row in set (0.87 sec) mysql> explain SELECT * FROM keyvalue WHERE `id_non_key` = '666666'; +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ | 1 | SIMPLE | keyvalue | ALL | NULL | NULL | NULL | NULL | 950004 | Using where with pushed condition | +----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------------+ 1 row in set (0.00 sec)