Search the Community

Hi all I have a full text product search query that is built up via php and certain elements are changed based on the search query. The query runs well and the results are very accurate and what we want, however it takes roughly 0.5s - 1.5s to run, as this is used in an ajax search there is a noticeable delay. So im looking for some advice on how I can improve the query. I think I have isolated the problem, some of our products are named slightly different but to the end user are the same thing, so for example an mtb helmet is the same as a mountain bike helmet. As such I wrote in some php to add in the additional keywords when the opposite is being search for. SELECT Product_Name, URL, ImageURL, StockPrice, Brand , MATCH (Product_NAME) AGAINST ('2014') + Rating * 0.1 + views * 0.1 + addedtobasket + MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mtb*' IN BOOLEAN MODE) + #This line here is added when the term mountain is search for MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mountain* +bike* +helmet*' IN BOOLEAN MODE) as Score FROM products WHERE MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mtb*' IN BOOLEAN MODE) + #This line here is added when the term mountain is search for MATCH (Product_Name,Cat1,Cat2,Cat3,Colour,Size) AGAINST ('+mountain* +bike* +helmet*' IN BOOLEAN MODE) AND Cat1 != 'Admin' AND Status = 'True' AND Cat1 NOT LIKE '%prodimg%' AND Stock_Level > 0 AND StockPrice > 0 AND date_updated > '2013-09-26 10:48:03' AND ProductID != '' GROUP BY ProductID ORDER BY Score DESC, Rating DESC When the addition line is added the query takes roughly 0.875s to run, without it comes back as 0.05s there is a large improvement on speed but an impact on the results. I have the following index as well : Any suggestions? Thanks.