Meaning of "Select tables optimized away" in MySQL Explain plan

user_223196 0
Viewed 22044

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.

sql-execution-plan query-optimization mysql sql performance
7 Answers

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_count​s you should be using SUM(comment_count), and you won't get the ‘optimized away’ behaviour.

user_189360

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

user_1351520

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.

doofledorfer_316410

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

user_13642900
Related