Disadvantages of MySQL memory tables

MySQL memory table so that we often use, but MySQL memory table is not a universal tool to improve read performance, in some cases, MySQL memory table may be slower than the actual table Type of B-TREE.

 
 

  
  
CREATE TABLE `mem_test` (                                                               
  
  
            `id` int(10) unsigned NOT NULL DEFAULT '0',                                           
  
  
            `name` varchar(10) DEFAULT NULL,                                                      
  
  
            `first` varchar(10) DEFAULT NULL,                                                     
  
  
            PRIMARY KEY (`id`),                                                                   
  
  
            KEY `NewIndex1` (`name`,`first`)                                                      
  
  
          ) ENGINE=MEMORY ;  
  
  
 
  
  
CREATE TABLE `innodb_test` (                     
  
  
               `id` int(10) unsigned NOT NULL DEFAULT '0',   
  
  
               `name` varchar(10) DEFAULT NULL,               
  
  
               `first` varchar(10) DEFAULT NULL,              
  
  
               PRIMARY KEY (`id`),                            
  
  
               KEY `NewIndex1` (`name`,`first`)               
  
  
             ) ENGINE=InnoDB;  
  
  
 
 
 

For example:
1: In the case of = or <=>, it is fast, but in the case of <or>, it does not use an index

 
 

  
  
mysql--root@localhost:17db 07:33:45>>explain select * from mem_test where id>3;  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| 1 | SIMPLE      | mem_test | ALL | PRIMARY       | NULL | NULL    | NULL |   15 | Using where |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
  
  
mysql--root@localhost:17db 07:33:49>>explain select * from innodb_test where id>3;  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
  
  
| id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra       |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
  
  
| 1 | SIMPLE      | innodb_test | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
 
 

2: it cannot be used in order by to increase the speed.

 
 

  
  
mysql--root@localhost:17db 07:33:55>>explain select * from innodb_test order by id;  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
  
  
| id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
  
  
| 1 | SIMPLE      | innodb_test | index | NULL          | PRIMARY | 4       | NULL |   15 |       |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
  
  
1 row in set (0.00 sec)  
  
  
 
  
  
mysql--root@localhost:17db 07:34:27>>explain select * from mem_test order by id;  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
  
  
| id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra          |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
  
  
| 1 | SIMPLE      | mem_test | ALL | NULL          | NULL | NULL    | NULL |   15 | Using filesort |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
 
 

3: the number of rows between two values cannot be determined.

 
 

  
  
mysql--root@localhost:17db 07:37:14>>explain select count(1) from mem_test where id>3 and id<6;  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| 1 | SIMPLE      | mem_test | ALL | PRIMARY       | NULL | NULL    | NULL |   20 | Using where |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
  
  
mysql--root@localhost:17db 07:40:35>>explain select count(1) from innodb_test where id>3 and id<6;  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
  
  
| id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra                    |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
  
  
| 1 | SIMPLE      | innodb_test | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |  
  
  
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
 
 

4: hash scanning can be used only when all columns are specified for multi-column indexes, while B-tree can indeed use the leftmost side of the index to find

 
 

  
  
mysql--root@localhost:17db 07:37:07>>explain select * from innodb_test where name='b';  
  
  
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
  
  
| id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra                    |  
  
  
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
  
  
| 1 | SIMPLE      | innodb_test | ref | NewIndex1     | NewIndex1 | 33      | const |    8 | Using where; Using index |  
  
  
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
  
  
1 row in set (0.00 sec)  
  
  
 
  
  
mysql--root@localhost:17db 07:37:10>>explain select * from mem_test where name='b';  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
| 1 | SIMPLE      | mem_test | ALL | NewIndex1     | NULL | NULL    | NULL |   20 | Using where |  
  
  
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
  
  
 
 
 

Of course, you can also manually add btree to the memory table

 
 

  
  
CREATE INDEX BTREE_index USING BTREE on mem_test(name,first)  
  
  
mysql--root@localhost:17db 03:36:41>>explain select * from mem_test where name='b';  
  
  
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
  
  
| id | select_type | table    | type | possible_keys         | key         | key_len | ref   | rows | Extra       |  
  
  
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
  
  
| 1 | SIMPLE      | mem_test | ref | NewIndex1,BTREE_index | BTREE_index | 33      | const |    9 | Using where |  
  
  
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
  
  
1 row in set (0.00 sec)  
 
 

Haha, it also uses indexes.
Therefore, it is important to select an appropriate storage engine.
 

Advantages and disadvantages of MySQL independent tablespace

Case sensitivity of mysql Databases

Restoration of MySQL MyISAM Table Structure

Restoration of MySQL InnoDB table structure

In-depth parsing MySQL Association Table Creation