List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 10 2009 5:05pm
Subject:Re: slow select when using VIEW
View as plain text  
In the last episode (Jun 10), Yariv Omer said:
> I have created the following 2 views:

> CREATE VIEW `cpes_noise_num` AS
[ big view]
> 
> CREATE VIEW `my_connect` AS
[ big view joining on cpes_noise_num ]
> 
> when I am trying to do something like:
> SELECT count(*) from my_connect
> 
> It takes 1 minute to return while doing the same query by explicitly using
> directly the select states above without the VIEW return after 1 second.
> 
> Can it be that the VIEW doesn't work with the tables indexes or something
> like that?

Mysql's view optimization is very rudimentary.  If it can trivially
substitute the view definition into your original query, it will do so;
otherwise it has to create a temporary table containing the view, and
reference that table instead.  "EXPLAIN SELECT count(*) from my_connect"
should make it obvious which one mysql is using in your case.

http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html

You'll probably have to embed your cpes_noise_num view inside the my_connect
view as a subquery instead.

-- 
	Dan Nelson
	dnelson@stripped
Thread
slow select when using VIEWYariv Omer10 Jun
  • Re: slow select when using VIEWDan Nelson10 Jun