First up, I'm sure you don't need telling you should be using CFQUERYPARAM for your query variables... (Of course, you may have just omitted that to simplify your example).
It's not the easiest query to optimise; have you considered a different approch: make a Verity index of your products. It's not something I've done much of myself, but it should also give you other benefits such as better returns for near matches... (If Verity isn't a good way to go for this, I'm sure someone will correct me...) Seb Seb Duggan Web & ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com ---------------------------------------- From: "Mike Little" <m...@nzsolutions.co.nz> Sent: 16 December 2008 09:06 To: "cf-talk" <cf-talk@houseoffusion.com> Subject: product search query - advice needed hi guys, i have a product table with over 500 records in it. each product is linked to a category and brand. when a user inputs a search i need all three elements searched (plus other fields as you will see in query below). at the moment, the query is not the fastest of queries (database is mysql). i was wondering if anyone can see any obvious mistakes i am making with this function... pid = product id cid = category id bid = brand id SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability FROM tblProducts P INNER JOIN tblProductDesc PD ON P.pid = PD.pid INNER JOIN tblProductCatLink PCL ON P.pid = PCL.pid INNER JOIN tblCat C ON PCL.cid = C.cid INNER JOIN tblCatDesc CD ON C.cid = CD.cid INNER JOIN tblBrand B ON P.bid = B.bid WHERE ((P.code LIKE '%searchstring%') OR (PD.ptitle LIKE '%searchstring%') OR (PD.pdesc LIKE '%searchstring%') OR (PD.fill_type LIKE '%searchstring%') OR (PD.summary LIKE '%searchstring%') OR (PD.features LIKE '%searchstring%') OR (CD.ctitle LIKE '%searchstring%') OR (B.btitle LIKE '%searchstring%')) AND P.display = 1 GROUP BY P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability ORDER BY PD.ptitle ASC thanks heaps. mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
