51

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.

Chandra Patni
  • 17,347
  • 10
  • 55
  • 65
  • I got the same result on InnoDB table when doing a query similar to: SELECT MAX(k3) FROM t1 WHERE k1='1' AND k2='2', where k1,k2 and k3 are all parts of a key. – Ghostrider Jul 23 '10 at 23:05

7 Answers7

55

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.

Leolo
  • 1,327
  • 9
  • 14
30

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.

bobince
  • 528,062
  • 107
  • 651
  • 834
  • 46
    If this was every true, it isn't now. I get that message from an EXPLAIN on a single Innodb table -- MySQL 5.5.17. – Riedsio Sep 30 '11 at 19:54
  • I also get that message from EXPLAIN from a SELECT on a single InnoDB table. – Eric R. Rath May 04 '12 at 18:52
  • 1
    isn't select count(fieldname) supposed to be more efficient than select count(*)? – jsh Jan 06 '14 at 15:26
  • This does happen with other engines including InnoDB also. I am actually encountering it now. – Christopher McGowan Aug 24 '15 at 05:00
  • 7
    I am seeing this with an InnnoDB table (MySQL 5.7) - so apparently the part "*The same won't happen on other storage engines*" is not true at least not any longer. –  Sep 12 '16 at 10:07
  • in addition to what @Riedsio said I get that on `select max` which mean that it doesn't occur on simply counting the rows but it getting data from that row as well. – user10089632 Dec 25 '17 at 10:42
  • 1
    This message can appear for InnoDB table based queries that have an index that covers the entirety of the query, thus making it behave like a MyISAM table in the sense of not having to touch the actual table. – egalvan10 Feb 11 '19 at 23:34
10

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
8

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.)'

Devang Mehta
  • 137
  • 1
  • 5
1

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.

Mike W.
  • 11
  • 1
0

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.

dkretz
  • 37,399
  • 13
  • 80
  • 138
0

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

Dean Thomsen
  • 139
  • 2
  • 7