List:General Discussion« Previous MessageNext Message »
From:Yariv Omer Date:June 10 2009 12:22pm
Subject:slow select when using VIEW
View as plain text  
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


Thread
slow select when using VIEWYariv Omer10 Jun
  • Re: slow select when using VIEWDan Nelson10 Jun