Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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     | <derived2> | 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 << here >>. 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.

share|improve this answer

For any given view mydb.myview, you can do

  • SHOW CREATE VIEW mydb.myview\G
  • SHOW CREATE TABLE mydb.myview\G (This works because a view appears in information_schema.tables with engine IS NULL)

Nevertheless, you will not see an ALGORITHM defined. Why ?

This was addressed in 2006 in a bug going back to MySQL 5.0.25

http://bugs.mysql.com/bug.php?id=16832

According to the end of that bug report

The VIEW_DEFINITION column of the INFORMATION_SCHEMA VIEWS table now contains information about the view algorithm.

There were two patches for this:

Apparently, it got lost in the shuffle over the years.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.