What is the query plan for a running query?
Because there's number of cases where you can't use explain to find out which plan the running query is using.
One reason Mark pointed out in his article is when query using temporary table in join condition, you don't have the temporary table definition as well as the actual data got populated in it. Even you could get the create table statement for the temp table, the data you populated into it may not same as that running query depends on how dynamic your data is.
Another reason I can think of is similar to bind variable peeking issue in Oracle database. When you have skew in your data, different bind variable could result different SQL plan as demonstrated below.
So if that case, explain can't really help you unless you know what bind variable was used at the time.
mysql> select table_type, count(*) from test_plan group by table_type;
+-------------+----------+
| table_type | count(*) |
+-------------+----------+
| BASE TABLE | 4910 |
| SYSTEM VIEW | 95 |
+-------------+----------+
2 rows in set (0.01 sec)
mysql> explain select * from test_plan where table_type='BASE TABLE' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_plan
type: ALL
possible_keys: table_type_inx
key: NULL
key_len: NULL
ref: NULL
rows: 5017
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from test_plan where table_type='SYSTEM VIEW' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_plan
type: ref
possible_keys: table_type_inx
key: table_type_inx
key_len: 194
ref: const
rows: 95
Extra: Using where
1 row in set (0.00 sec)
