Meaning of "Select tables optimized away" in MySQL Explain plan_问答_开发者

What is the meaning of Select tables optimized away in MySQL Explain plan?

explain select count(comment_count) from wp_posts;

+----+-------------+---------------------------+-----------------------------+
| id | select_type | table,type,possible_keys, | Extra                       |
|    |             | key,key_len,ref,rows      |                             |
+----+-------------+---------------------------+-----------------------------+
| 1  | SIMPLE      | all NULLs                 开发者_开发问答| Select tables optimized away| 
+----+-------------+---------------------------+-----------------------------+
1 row in set (0.00 sec)

Note: explain plan output edited for legibility.

From the MySQL documentation:

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

Basically this means your query uses data that is directly available to MySQL and the query will run in constant time.

It means you have done a query that does nothing more than count the number of rows in a table, and that table is a MyISAM table. MyISAM tables are stored with a separate row count, so to do this query MySQL doesn't need to look at any of the table row data at all. Instead it immediately returns the pre-calculated row count. Hence the table access is ‘optimized away’ and the query is lightning-fast.

The same won't happen on other storage engines in MySQL such as InnoDB. But really, you want to be using InnoDB and not MyISAM in most cases for a variety of other reasons. (And even without the row count optimisation this kind of query is very, very fast.)

select count(comment_count) from wp_posts;

Is that what you really meant to do? That's the same as just SELECT COUNT(*)... (assuming comment_count can't be NULL, which it can't be or you wouldn't have got the optimisation). If you want a total of the comment_counts you should be using SUM(comment_count), and you won't get the ‘optimized away’ behaviour.

It means the table is completely optimized out of the query. You can’t get any better than that.

The accepted answer and the most upvoted answer seem to suggest that this type of explanation only applies to MyISAM tables. But I am seeing this with an InnoDB table.

I looked up the mysql documentation here for version 5.6, https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_extra

I don't see that the explanation is restricted to MyISAM. But in the specific case of COUNT(*), there is a note that says that this type of explanation will become valid in case of the table being MyISAM.

'For storage engines that maintain an exact row count per table (such as MyISAM, but not InnoDB), this Extra value can occur for COUNT(*) queries for which the WHERE clause is missing or always true and there is no GROUP BY clause. (This is an instance of an implicitly grouped query where the storage engine influences whether a deterministic number of rows can be read.)'

For innodb tables I've seen the "Select tables optimized away" when looking for a min or max of a column that has auto_increment. The information_schema.tables keeps the maximum auto_increment so it's easy for the optimiser to just look there and never touch the user table. It won't work for things like count because there could be gaps so the optimizer has to go to the user table for the answer.

Hard to say without seeing your query, but this would be the consequence if you for instance selected a constant value --

SELECT 1 FROM atable

or one or more of your tables isn't required to answer the question.

I'm not convinced that the table is completely optimized out of the query. This query "select 'wm.task_inst_params' as table_name, count(id) from wm.task_inst_params;" takes 18 minutes to run. The table has 224788138 rows.

And if I try to get an explain plan to see why it takes so long, I get this: "Can't explain plan: Select tables optimized away".

innodb_version 8.0.11