php - search query slow, recordset based on view. how to speed it up? -

i have recordset based on view in mysql use return search results painfuilly slow (consistently 21 seconds!). similar search in same environment takes under second.

i fear view slowing things down since have 4 left joins , 1 subquery in there make related data available in search.

is there general guidance speeding query when using view? have researched indexing seems not allowed in mysql in views.

thanks in advance suggestions.

the code create view:

create view vproducts2   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` type, categories.category category,       `watchbrands`.`brand` brand, productfeatures.productfeaturevalue size,       (select productimages.image         productimages         productimages.productid = products2.productid         limit 1     ) pimage   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         , productfeatures.featureid = 1    

you need ensure have indexes on underlying tables, not on view. view should use such tables.

the first index screams out on productimages(productid, productimage). speed subquery in select clause.

you should have primary key indexes primary keys on tables . . . categories(categoryid), producttype(typeid), watchbrands(brandid), , (i think) productfeatures(productid, featureid).