Nothing is glaring, but you would definitely want to look at optimizing MYSQL for this. You can create indexes, views, etc that should help with the speed. Take a look at http://www.learn-mysql-tutorial.com/ for a bunch of optimization tips/tricks.
> 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 > > <cfquery> > 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 > </cfquery> > > 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:316808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
