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