Determining what algorithm MYSQL View is using

When creating a MySQL View, the algorithm is set to UNDEFINED.

How do I see which algorithm MySQL is choosing for the query ?

I tried using "EXPLAIN", but it is not obvious from the results what algorithm was used for the query.

If the view is being processed with the TEMPTABLE algorithm, the view will be treated by the optimizer as if it were written as a subquery in the from clause (derived table) and should show up in the EXPLAIN output as a derived table.

Here's an example of a view that was explicitly defined with the TEMPTABLE algorithm. Two tables show up in the query plan, although this view was just defined as SELECT * from a single table.

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     |  | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
|  2 | DERIVED     | task       | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

When the view is defined with the UNDEFINED algorithm, the optimizer...

prefers MERGE over TEMPTABLE if possible

— http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html (also previous versions ~sqlbot)

Aggregate functions/group by/having, distinct, limit, unions, scalar subqueries, and views that return literals only (not from a table) require TEMPTABLE be used... a view without those should be processed as MERGE consistently when the algorithm on the view definition is the UNDEFINED algorithm.

Because of the nature of the two view algorithms, the only one that can use indexes on the underlying tables to find rows matching the outer WHERE -- not one inside the view definition, but the one SELECT ... FROM view WHERE >. If the EXPLAIN output shows those indexes being used the way you would typically hope, then you're getting MERGE.

I generally define views as MERGE explictly. The server will accept and save the definition, but somewhat quietly change it back to UNDEFINED and throw a warning if it can't be actually be processed with MERGE.

mysql> CREATE ALGORITHM=MERGE VIEW priority_counts AS SELECT priority_id, COUNT(1) AS quanity FROM task GROUP BY priority_id;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.08 sec)

SHOW CREATE VIEW subsequently shows ALGORITHM=UNDEFINED.

Of course, when MERGE is accepted, it does show in the view definition, as does TEMPTABLE when it's explicitly declared that way.

StackExchange Database Administrators Q#54481, answer score: 4