一个查询结果集放到视图后查询很慢

zqb666 发布于 2014/07/23 20:26
阅读 2K+
收藏 0

 mysql  数据库,一个很复杂的 查询比如 select * from  a left join b ...等 这个单独查询很快的 但是把他放到视图里 通过 select * from 视图名 查询很慢

这个怎么解决


第三方平台只支持查询我们这边的视图

这个视图是:DELIMITER $$


USE `test`$$


DROP VIEW IF EXISTS `fullsearch_product_view`$$


CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW `fullsearch_product_view` AS 
SELECT
  `products`.`minimumunit`      AS `minimumunit`,
  `products`.`minimum`          AS `minimum`,
  `products`.`number`           AS `number`,
  `products`.`psid`             AS `psid`,
  (SELECT
     GROUP_CONCAT(`products_cav`.`cavname` SEPARATOR ',') AS `cavname`
   FROM `products_cav`
   WHERE (`products_cav`.`psid` = `products`.`psid`)) AS `cavname`,
  (SELECT
     GROUP_CONCAT(`products_cav`.`caname`,_utf8':',`products_cav`.`cavname` SEPARATOR '|') AS `cavnameshow`
   FROM `products_cav`
   WHERE (`products_cav`.`psid` = `products`.`psid`)) AS `cavnameshow`,
  `products`.`uid`              AS `uid`,
  `users_detailed`.`qq`         AS `qq`,
  `users_detailed`.`sfkqwp`     AS `sfkqwp`,
  `users_detailed`.`perfect`    AS `perfect`,
  `products`.`cid`              AS `cid`,
  `company`.`cname`             AS `cname`,
  `company`.`ptid`              AS `ptid`,
  `company`.`province`          AS `province`,
  `company`.`city`              AS `city`,
  `products`.`ps_name`          AS `ps_name`,
  `products`.`cname_channeling` AS `cname_channeling`,
  `products`.`caid_channeling`  AS `caid_channeling`,
  `products`.`spfl`             AS `spfl`,
  `products`.`desc`             AS `desc`,
  `products`.`created_date`     AS `created_date`,
  `products`.`model`            AS `model`,
  `products`.`urlimg1`          AS `urlimg1`,
  `products`.`bing`             AS `bing`,
  `products`.`average`          AS `average`,
  `products`.`unit`             AS `unit`,
  `company`.`integrity`         AS `integrity`,
  `company`.`age`               AS `age`,
  `company`.`main_business`     AS `main_business`,
  `products`.`update_date`      AS `update_date`,
  `products`.`urlimg2`          AS `urlimg2`,
  `products`.`urlimg3`          AS `urlimg3`,
  `products`.`avg_unit`         AS `avg_unit`,
  `products`.`databoost`        AS `databoost`,
  `maxdatetime`(
`products`.`update_date`,`users_detailed`.`update_date`,`company`.`update_date`,NULL)  AS `fullsearchtimestamp`,
  `users_detailed`.`user_url`   AS `shop_url`,
  `business_model`.`bm_name`    AS `bm_name`,
  `business_model`.`bmid`       AS `bmid`,
  `users_detailed`.`wangwang`   AS `wangwang`
FROM (((`products`
     LEFT JOIN `users_detailed`
       ON ((`products`.`uid` = `users_detailed`.`uid`)))
    LEFT JOIN `company`
      ON ((`products`.`cid` = `company`.`cid`)))
   LEFT JOIN `business_model`
     ON ((`company`.`bmid` = `business_model`.`bmid`)))
WHERE ((`products`.`state` = 1)
       AND (`products`.`audit` = 2)
       AND (`products`.`islate` = 1)
       AND (`users_detailed`.`sfkqwp` = 1))$$


DELIMITER ;

麻烦哪个大神帮忙看看 

加载中
0
realanan
realanan
mysql在处理复杂视图的时候,就会先将视图查询出来存到临时表,然后再从临时表执行你的查询。是有可能比不用视图时慢。
你可以用explain看下查询计划
返回顶部
顶部