MySQL内存表测试
时间：2010-04-16 来源：ubuntuer
delimiter //
create procedure insert_data() begin declare i INT default 0; while i<5000 do insert into huge_data values(i,concat("kenthy",i),i%26+1); set i=i+1; end while; end;//
delimiter ; mysql> create table if not exists huge_data(id INT UNSIGNED, name VARCHAR(16) not null, age INT UNSIGNED); select * from huge_data where id>1000 && id<1002;
大概0.14s create table tmp ENGINE=MEMORY select * from huge_data
select * from tmp where id>1000 && id<1002;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1001 | kenthy1001 | 14 |
+------+------------+------+
1 row in set (0.00 sec) mysql> explain extended select * from tmp where id>1000 && id<1002;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tmp | ALL | NULL | NULL | NULL | NULL | 5000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> explain extended select * from data_huge where id>1000 && id<1002;
ERROR 1146 (42S02): Table 'test.data_huge' doesn't exist
mysql> explain extended select * from huge_data where id>1000 && id<1002;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | huge_data | ALL | NULL | NULL | NULL | NULL | 5000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec) explain的结果是一样的,只是表不一样
create procedure insert_data() begin declare i INT default 0; while i<5000 do insert into huge_data values(i,concat("kenthy",i),i%26+1); set i=i+1; end while; end;//
delimiter ; mysql> create table if not exists huge_data(id INT UNSIGNED, name VARCHAR(16) not null, age INT UNSIGNED); select * from huge_data where id>1000 && id<1002;
大概0.14s create table tmp ENGINE=MEMORY select * from huge_data
select * from tmp where id>1000 && id<1002;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1001 | kenthy1001 | 14 |
+------+------------+------+
1 row in set (0.00 sec) mysql> explain extended select * from tmp where id>1000 && id<1002;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tmp | ALL | NULL | NULL | NULL | NULL | 5000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> explain extended select * from data_huge where id>1000 && id<1002;
ERROR 1146 (42S02): Table 'test.data_huge' doesn't exist
mysql> explain extended select * from huge_data where id>1000 && id<1002;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | huge_data | ALL | NULL | NULL | NULL | NULL | 5000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec) explain的结果是一样的,只是表不一样
相关阅读 更多 +
