搜索查询慢,记录集基于视图。 如何加快速度?

I have a recordset based on a view in MySQL that I use to return search results but it is painfuilly slow (consistently 21 seconds!). A similar search in the same environment takes under a second.

I fear that it is the view that is slowing things down since I have four left joins and one subquery in there to make related data available in the search.

Is there any general guidance for speeding up a query when using a view? I have researched indexing but it seems that is not allowed in MySQL in views.

Thanks in advance for any suggestions.

The code to create my view:

CREATE VIEW vproducts2 AS  
SELECT products2.productid, products2.active, products2.brandid,
    products2.createddate, products2.description, products2.inventorynum,
    products2.onhold, products2.price, products2.refmodnum, products2.retail,
    products2.sefurl, products2.series, products2.sold,
    `producttype`.`type` AS type, categories.category AS category,  
    `watchbrands`.`brand` AS brand, productfeatures.productfeaturevalue AS size,  
    (SELECT productimages.image
        FROM productimages
        WHERE productimages.productid = products2.productid
        LIMIT 1
    ) AS pimage  
FROM products2  
    LEFT JOIN producttype ON producttype.typeid = products2.typeid  
    LEFT JOIN categories ON categories.categoryid = products2.categoryid  
    LEFT JOIN watchbrands ON watchbrands.brandid = products2.brandid  
    LEFT JOIN productfeatures ON productfeatures.productid = products2.productid
        AND productfeatures.featureid = 1   
查看全部
dousi1906
dousi1906
2013/05/21 14:17
  • search
  • php
  • mysql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复