slow select when using VIEW

Hi


I have created the following 2 views:
CREATE VIEW `cpes_noise_num` AS
SELECT cpes_dsl_line_stats.id_cpes,
sum(IF(cpes_dsl_line_stats.snr_downstream <
(SELECT snr_downstream FROM admin_configs WHERE admin_id =0),1,0)) AS
snr_downstream, sum(IF((cpes_dsl_line_stats.bit_errors /
cpes_dsl_line_stats.bit_errors*100) >
(SELECT bit_error_percentage FROM admin_configs WHERE
admin_id=0),1,0)) AS bit_errors
FROM cpes_dsl_line_stats WHERE time > DATE_SUB(NOW(),INTERVAL
(SELECT connect_days FROM admin_configs WHERE admin_id=0) DAY)
AND time > (SELECT count_connects_from FROM cpes
WHERE cpes.id=cpes_dsl_line_stats.id_cpes)
GROUP BY id_cpes;

CREATE VIEW `my_connect` AS
SELECT cpe_log.id_cpes, cpes.cpe_id, users.id user_id, users.email
user_email, (snr_downstream+bit_errors) AS noise_issues, COUNT(*)
connections
FROM cpe_log USE INDEX (time) JOIN cpes ON cpe_log.id_cpes=cpes.id LEFT
JOIN users ON cpe_log.id_cpes=users.id_cpes LEFT JOIN cpes_noise_num ON
cpes_noise_num.id_cpes = cpes.id
WHERE ((time > DATE_SUB(NOW(),INTERVAL
(SELECT connect_days
FROM admin_configs
WHERE admin_id=0) DAY)
AND time > cpes.count_connects_from AND event='Connect')
OR cpes_noise_num.snr_downstream > 0 OR cpes_noise_num.bit_errors > 0)
AND cpes.ignore_connects=0
GROUP BY cpes.cpe_id HAVING COUNT(*) > (SELECT connect_count FROM
admin_configs WHERE admin_id=0) ORDER BY COUNT(*) DESC;


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?

Regards, Yariv



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: slow select when using VIEW

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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org