Chandra Patni Chandra Patni - 1 year ago 248
SQL Question

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)

explain plan
output edited for legibility.

Answer Source

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.