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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | 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; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 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) 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.
Well probably v 5.2 will be used to make all the new features stable and well integrated 😉
Based on your EXPLAIN output, it looks like Views inherit indices from the underlying tables (which would explain the speed difference). Probably the execution of the subselect is faster/less of a memory hog than creating the view, so I can see that you might want the two options to maximize performance in specific situations.
Do you get similar results in less contrived (more realistic) scenarios?
I would not call it Views Inherit indexes because views are not physical – they have no real data or indexes, they however describe a way to access data.
The difference is not inheriting indexes but as I wrote different method of execution – Views are executed (in this case) by query rewriting, so effectively query becomes same as on base table. Inline views/derived tables however can’t do it they always have to materialize table.
Creating view you can also force it to use TEMPTABLE for query execution. See
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Regarding behavior in real cases – this is simplification based on real production cases.
Note: It applies to subselects in FROM clause only. Other kind of subselects is very different story.
I wonder what happens if you use a view that cannot be easily rewritten.
eg: GROUP BY or have a SUM in it.
Now, do a “select * from view where summed_column = 50” or something.
Will this be re-written? And if so, what IS the final result of the re-written query?
Besides, why DO derived tables get materialized anyway? The optimizer is free to re-write queries including these as well, right?
—
Martijn Tonies
Upscene Productions
Martijn,
First – you’re right. There is no reason for derived tables to be materialized. It is design deficiency in MySQL 5.0.
Speaking about Views with group by – good question. MySQL is currently able to execute via query merge only simple views, more complicated views as ones with group by require temporary table even if it is possible to avoid it. Hopefully it will be improved in the future:
mysql> create view v1 as select count(*) cnt, k from test group by k;
Query OK, 0 rows affected (0.03 sec)
mysql> explain select * from v2 where k=7 limit 5; | ALL | NULL | NULL | NULL | NULL | 1638400 | Using where |
+—-+————-+————+——-+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+—————+——+———+——+———+————-+
| 1 | PRIMARY |
| 2 | DERIVED | test | index | NULL | k | 772 | NULL | 1638400 | Using index |
+—-+————-+————+——-+—————+——+———+——+———+————-+
2 rows in set (6.88 sec)
As you see full temporary table is used, there is index on k but it only allows to do index scan instead of full table scan.
Interesting, thanks for trying.
—
Martijn Tonies
Upscene Productions
we are currently using mysql 5.037(27)
it looks like when you create a view on a table and then use this view in query with a join or where statement with another table indexes
from a view(first original table) are not used.Run the same query replacing view with a table it is based and result is in several times faster.
We ran into this problem just recently on a real world query. In this case the query was of this form:
select * from (big inner query) foo order by a, b, c
The choice to use this query syntax was for programming brevity – it’s easier to rename the output columns from the big inner query using the outer query instead. Alas, though, the nasty version of this query took over 2.5 minutes to run on 53,000 record scans. The version without the outer select ran in 9/10th of a second on the same record space.
The EXPLAIN clearly showed that the outer select was doing a full table scan on the results instead of using the indices.
Unbelievable. I ran the exact same nasty query in Microsoft SQL Server 2000 and it performed as expectd – lightning fast execution. The execution plan showed the effective use of table indices and much smarter insertion of the sub-query’s grouping clauses.
MySQL needs quite a bit of real-world tuning to make it a believable enterprise database. You get what you pay for, right?
ç å®è”盟网
ä¸å›½ç å®è”盟网(zblmw.com)是一家æœåŠ¡äºŽä¸å›½å¤§é™†åŠå…¨çƒåŽäººç¤¾ç¾¤çš„领先在线ç å®åª’体åŠå¢žå€¼èµ„讯æœåŠ¡æ供商。ä¸å›½ç å®ç½‘站拥有多家地区性网站，以æœåŠ¡å¤§ä¸åŽåœ°åŒºä¸Žæµ·å¤–åŽäººä»¥åŠç å®ä¼ä¸šä¸ºå·±ä»»ï¼Œé€šè¿‡ä¸ºå¹¿å¤§ç½‘民和政府ä¼ä¸šç”¨æˆ·æ供网络媒体åŠå¨±ä¹ã€åœ¨çº¿ç”¨æˆ·ä»˜è´¹å¢žå€¼/æ— çº¿å¢žå€¼æœåŠ¡å’Œç”µå政务解决方案ç‰åœ¨å†…的一系列æœåŠ¡ã€‚
专业ç å®é—¨æˆ·â€”—ä¸å›½ç å®ç½‘站预计2008年在全çƒèŒƒå›´å†…注册用户超过500万，日æµè§ˆé‡èƒ½æœ€é«˜çªç ´8000万次，将æˆä¸ºä¸å›½å¤§é™†åŠå…¨çƒåŽäººç¤¾ç¾¤ä¸æœ€å—推崇的行业互è”网å“牌。
高效的整åˆè¥é”€æœåŠ¡â€”—å‡å€Ÿé¢†å…ˆçš„技术和优质的æœåŠ¡ï¼Œä¸å›½ç å®ç½‘站会深å—广大网民的欢迎并能享有æžé«˜çš„声誉。
http://www.zblmw.com
Ari: Did you try immediently running it again with a different where this is where views shine. Yes unless specifically made not to a view will run the exact select stored in the view. But untill the table it is based upon changes relative to that view a temporary table will be stored containing the full view. As the view temp table won’t need recreating a second run with where would be faster than the second run of a regular select because you have less columns to deal with. A view will generally retain it parents indexes.
John, that is incorrect. Views do not store their data. You are mixing the TEMPTABLE algorithm (which creates an internal temp table for the duration of the query) and the query cache (which stores result sets until underlying tables change or other conditions require discarding them). And views only permit usage of the underlying table’s indexes if the MERGE algorithm is used.
Baron: You know what your right. Somehow in the past I was able to get a view to do this, think it was for a aggregated value (view was an aggregate) which was then trimmed down in queries to the view to be pieces of the aggregate. Was able to get better performance at the time on multiple queries in a row (first query of a connection was slow later queries were instant) then a straight query to the table as the view was holding the aggregate data used for selection of its parts. Can’t seem to figure out how I did it now as I didn’t specify any special notation to it (like TEMPTABLE).
Hi,
Is the problem still existing in actual versions of MySQL, or is it solved ?
Thank you.
I experienced this problem on OS X 10.6.8 with the most recent version of MySQL (5.5.27). It still exists. Derived tables (at least for me) are not indexed. I’m no longer using derived tables, and using views (which are indexed) instead.
i would appreciate your comments on this script: taken from:
http://phptechnicalgroups.blogspot.co.il/2013/05/simple-mysql-and-php-prodcuts-and-cart.html
create dynamic main menu and sub menu using php and mysql
<?php
CREATE TABLE
menu(idint(11) NOT NULL auto_increment,labelvarchar(50) NOT NULL default '',linkvarchar(100) NOT NULL default '#',parentint(11) NOT NULL default '0',sortint(11) default NULL,PRIMARY KEY (
id))——————————————————————————————————
$mysql=mysql_connect('127.0.0.1','root','');
mysql_select_db('test',$mysql);
function display_menu($parent, $level) {
$result = mysql_query("SELECT a.id, a.label, a.link, Deriv1.Count FROM
menua LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROMmenuGROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent);echo "”;
while ($row = mysql_fetch_assoc($result)) {
if ($row[‘Count’] > 0) {
echo “” . $row[‘label’] . ““;
display_menu($row[‘id’], $level + 1);
echo “”;
} elseif ($row[‘Count’]==0) {
echo “” . $row[‘label’] . ““;
} else;
}
echo “”;
}
display_menu(0, 1);
?>
Posted by bikash ranajan nayak at 10:30 AM
Thanks the post helped me shave off 4 seconds from a heavily used and very ugly query that had 4 derived tables in it.
Hi peter,
i have on query , while use EXPLAIN it is returning result as follow :
type : Range
key : Primary
ref : NULL
rows : 390056,
i am concern about row lookup, do you think it is traversing too many rows? actually i had used “OR” in where clause for at least 10 objects.
query time is okay for me its 0.008 seconds. do you think it can be a problematic when table grows in future? my estimation is about one billion rows in the same table.
Thanks
Thank you. Can you tell me if it still applies to MySQL 5.7 ?