oh and, those figures are ms per query as per CF debug info! ----- Original Message ----- From: "Gavin Cooney" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, October 14, 2003 4:46 PM Subject: Re: [cfaussie] Re: IN versus cfloop and where
> Hi, > > I tried a test. > > This in a table currently with 1.3 million records on mySQL. > > I ran two queries per page. One after another. Each using the code as > outlined below. > > OR IN > 496 516 (IN query before OR in page) > 484 648 (IN query before OR in page) > 492 528 (IN query before OR in page) > 641 488 (OR query before IN in page) > 653 312 (OR query before IN in page) > 516 212 (OR query before IN in page) > 493 179 (OR query before IN in page) > 1338 1371 (IN query before OR in page) > 654 1254 (IN query before OR in page) > 730 443 (IN query before OR in page) > 601 967 (IN query before OR in page) > > So that's pretty inconclusive where i'm standing. > > I'm leaving it with the OR type query, because the Db is already overloaded. > > Thanks > > Gav > > > ----- Original Message ----- > From: "Brett Payne-Rhodes" <[EMAIL PROTECTED]> > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > Sent: Tuesday, October 14, 2003 4:10 PM > Subject: [cfaussie] Re: IN versus cfloop and where > > > > Hi Gavin, > > > > I don't think version II will work with an 'AND' anyway. Perhaps an 'OR' > > would work. But I think version I is a better solution regardless. Maybe > > you could test both of them and let us know the definitive answer? > > > > hth, > > > > Brett > > B) > > > > Gavin Cooney wrote: > > > Hi all > > > > > > Anyone know which would be quicker on a huge (couple of million records) > > > on mySQL? > > > > > > UPDATE > > > table_name > > > SET > > > field1 = <cfqueryparam value="W" cfsqltype="CF_SQL_CHAR"> > > > WHERE > > > primary_key of table IN (<cfqueryparam > > > value="#some_list#" > > > cfsqltype="CF_SQL_NUMERIC" > > > list="Yes">) > > > > > > OR > > > > > > UPDATE > > > table_name > > > SET > > > field1 = <cfqueryparam value="W" cfsqltype="CF_SQL_CHAR"> > > > WHERE > > > 1=1 > > > <cfloop list="#some_list#" index="i"> > > > AND field1 = <cfqueryparam > > > value="#i#" > > > cfsqltype="CF_SQL_NUMERIC"> > > > </cfloop> > > > > > > Thanks for your help > > > > > > Gavin > > > --- > > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > > [EMAIL PROTECTED] > > > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > > > > > -- > > Brett Payne-Rhodes > > Eaglehawk Computing > > t: +61 (0)8 9371-0471 > > f: +61 (0)8 9371-0470 > > m: +61 (0)414 371 047 > > e: [EMAIL PROTECTED] > > w: www.ehc.net.au > > > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
