Why is const rather than system of type in mysql explain?

user_9568260 0
Viewed 61

mysql version: 5.7.33
mysql schema:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test_system
-- ----------------------------
DROP TABLE IF EXISTS `test_system`;
CREATE TABLE `test_system`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test_system
-- ----------------------------
INSERT INTO `test_system` VALUES (1, 'name');

SET FOREIGN_KEY_CHECKS = 1;

select:

mysql> EXPLAIN SELECT id,`name` FROM test_system WHERE id = 1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_system | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql doc:

  • system
    The table has only one row (= system table). This is a special case of the const join type.

The table has only one row in test_system.

Why result type is const rather than system?

Thanks in advance.

mysql
1 Answers

It's because test_system use the InnoDB engine. InnoDB doesn't maintain table sizes reliably, so the query optimizer can't be sure that the table has exactly 1 row.

When I use MyISAM it uses type=system rather than type=const.

MySQL [bbodb_test]> create table test_system (id int(11) not null primary key, name varchar(255)) engine=myisam;
Query OK, 0 rows affected (0.002 sec)

MySQL [bbodb_test]> insert into test_system values (1, 'name');
Query OK, 1 row affected (0.001 sec)

MySQL [bbodb_test]> explain select * from test_system where id = 1;
+----+-------------+-------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table       | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test_system | system | PRIMARY       | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------------+--------+---------------+------+---------+------+------+-------+
user_14918950
Related