From: Yariv Omer (yarivojungo.com)
Date: Wed Jun 10 2009 - 07:22:56 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]