Derived Tables and Views Performance

August 31, 2006

Author

Peter Zaitsev

Insight for Developers

Share this Post:

Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.
In MySQL 5.0 support for views was added.

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

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.

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

One more thing to watch for is the fact derived table is going to be materialized even to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever.

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

What does it mean in terms of performance:

Query on base table executes using index and it is very fast

mysql&gt; select * from test where i=5;
+---+----------------------------------+
| i | j                                |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row in set (0.03 sec)


Same query using derived table crawls:

mysql&gt; 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:

mysql&gt; create view v as select * from test;
Query OK, 0 rows affected (0.08 sec)

mysql&gt; 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

mysql&gt; 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&gt; 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     |  | ALL  | NULL          | NULL | NULL    | NULL | 1638400 | Using where |
|  2 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL | 1638400 |             |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
2 rows in set (54.90 sec)

Note how long it took just to execute explain for derived table

Query on base table executes using index and it 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:

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:

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

mysql> explain select * from v where i=5;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.02 sec)

mysql> explain select * from (select * from test) t where i=5;

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

2 rows in set (54.90 sec)

Note how long it took just to execute explain for derived table

So what does it mean in practice:

Avoid derived tables – If there is other way to write the query it will be faster in most cases. In many cases even separate temporary table will be faster as you can add proper indexes to the table in this case.

Consider using temporary views instead of derived tables If you really need to use subselect in from clause consider creating view using it in the query and dropping it after query was executed.

In any case it is pretty annoying gotcha which I hope MySQL will fix in next MySQL versions – the fact queries in this example behave differently is illogical and counter intuitive.