what is meaning of "Select tables optimized away" in mysql5.7 excution plan?

Question

i explain a query in mysql5.7.16, i can't understand what is meaning of "Select tables optimized away" in the plan. i am searching for a long time on google, but no use. who can give me ideas ?thanks!

the scenario is below:

(root@localhost)[(none)]>desc select count(F02) from S65.T6507;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

(root@localhost)[(none)]>show index from S65.T6507;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T6507 |          0 | PRIMARY  |            1 | F01         | A         |    18364472 |     NULL | NULL   |      | BTREE      |         |               |
| T6507 |          1 | F03      |            1 | F03         | A         |       69338 |     NULL | NULL   |      | BTREE      |         |               |
| T6507 |          1 | F02      |            1 | F02         | A         |    18364472 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

(root@localhost)[(none)]>show create table S65.T6507 \G
*************************** 1. row ***************************
       Table: T6507
Create Table: CREATE TABLE `T6507` (
  `F01` int(10) unsigned NOT NULL COMMENT 'xxx',
  `F02` int(10) unsigned NOT NULL COMMENT 'xxx',
  `F03` int(10) unsigned NOT NULL COMMENT 'xxx',
  `F04` decimal(20,2) unsigned NOT NULL COMMENT '..',
  `F05` decimal(20,2) unsigned NOT NULL COMMENT '..',
  `F06` decimal(20,2) unsigned NOT NULL COMMENT '..',
  `F07` int(10) unsigned DEFAULT NULL COMMENT '.',
  `F08` int(10) unsigned DEFAULT NULL COMMENT '..',
  PRIMARY KEY (`F01`),
  KEY `F03` (`F03`) USING BTREE,
  KEY `F02` (`F02`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xxx'
1 row in set (0.00 sec)

myql version:
mysql  Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using  EditLine wrapper
Server version:         5.7.16-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

score 0 · Answer 1 · answered Mar 19 '17 at 07:13

0

Since you're asking for the count of a column that's indexed, it doesn't need to count the rows in the the table, it can get the information directly from the index.

answered Mar 19 '17 at 07:13

Barmar

741,623
53
500
612

do you mean that the query using index to scan ? this table have three indexes: F02 , F03 and PRIMARY, how can i find out which one is used ? – elison Mar 20 '17 at 07:59
The only one that matters is the index on `F02`, since you write `COUNT(F02)`. – Barmar Mar 20 '17 at 15:19

score 0 · Answer 2 · answered Mar 19 '17 at 21:33

This is specific use case of that phrase is new with 5.7. The phrase was previously used for other situations where the answer could be found without touching more that what was needed to do the EXPLAIN (alias desc).

There is no "harm" in what it says.

A similar example involves an MyISAM table:

mysql> EXPLAIN SELECT COUNT(*) FROM mysql.user\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away

This was because MyISAM dead-reckoned the number of rows in a table.

what is meaning of "Select tables optimized away" in mysql5.7 excution plan?

2 Answers2