MySQL derived tables and views performance

Starting MySQL 4.1, MySQL had support for what are called tables, derived views or inline basically Clause.

In the MySQL 5.0 support for the views is added.

Starting with MySQL 4.1, it already supports subqueries of derived tables, online views, or basic from clauses.

These features are quite related to each other but how do they compare in terms of performance?

These features are related to each other, but what are the performance comparisons between them?

Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be Merged as it is quite the same task in terms of query optimization.

The derived table in MySQL 5.0 seems to be different from the way the view is implemented, although I think it should be the same on query optimization from the consolidated code base.

Derived Tables are still handled by materializing them at the temporary table, furthermore temporary table with no indexes (So, really do not want to join two derived tables for example).

Derived tables are still explicitly processed as temporary tables, and are temporary tables without indexes (so it is best not to connect 2 derived tables as in the example).

One more thing to watch of the fact derived table is going to, materialized even to execute EXPLAIN. So if you have do mistake in select with FROM clause, ie forgotten join condition for you might have EXPLAIN running.

On the other hand, the derived tables need to be handled explicitly, although only the EXPLAIN statements are executed. So if you make an error in the SELELCT operation in the FROM clause, such as forgetting to write the connection condition, then EXPLAIN may be running all the time.

Views on other hand does not have to is materialized and normally executed by rewriting the query. It only'll be materialized if query the merge is impossible or if requested by view creator.

The view is different, it does not have to be explicitly processed, but simply rewrite the query. Explicit processing is required only if a query cannot be merged or when an attempt is made to request a creator.

What does it mean in terms of performance:

This means that they differ in performance as follows:

PLAIN TEXT

Sql:

Query on base TABLE executes USING INDEX and it is very fast

Perform indexed queries on basic tables, which is very fast

mysql> SELECT * FROM test WHERE i=5;
+---+----------------------------------+
| i | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (0.03 sec)

Same query USING derived TABLE crawls:

Make the same query on a derived table, like an old cow pulling a broken car

mysql> SELECT * FROM (SELECT * FROM test) t WHERE i=5;
+---+----------------------------------+
| i | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (1 min 40.86 sec)
Query USING VIEW IS fast again:

In an attempt to inquire, and get up again

mysql> CREATE VIEW v AS SELECT * FROM test;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM v WHERE i=5;
+---+----------------------------------+
| i | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (0.10 sec)
Here are couple of explains IF you are curios

Here are 2 explain results that might surprise you.

Mysql> EXPLAIN SELECT * from v WHERE i=5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| ID | Select_type | TABLE | Type | Possible_keys | KEY | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | Test | Const | PRIMARY | PRIMARY | 4 | Const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in SET (0.02 sec)
Mysql> EXPLAIN SELECT * FROM (SELECT * from Test) T WHERE i=5;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| ID | Select_type | TABLE | Type | Possible_keys | KEY | Key_len | Ref | Rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL | 1638400 | USING WHERE |
| 2 | DERIVED | Test | All | NULL | NULL | NULL | NULL | 1638400 | |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
2 rows in SET (54.90 sec)