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

Question

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   

Answer 1

You need to ensure that you have indexes on the underlying tables, not on the view. The view should use such tables.

The first index that screams out is on productimages(productid, productimage). This will speed up the subquery in the select clause.

You should also have primary key indexes for what look like primary keys on all the tables . . . categories(categoryid), producttype(typeid), watchbrands(brandid), and (I think) productfeatures(productid, featureid).