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
