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=arch...@jab.org
