Just as a note on performance, When thinking about using OR, you should consider splitting it into separate queries and using a UNION or UNION ALL to join them together.
Rob -----Original Message----- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: 22 October 2007 16:35 To: CF-Talk Subject: Re: Which query would be better? Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Dominic On 22/10/2007, Ben Doom <[EMAIL PROTECTED]> wrote: > > Offhand, I'd guess using the "in" syntax, if only because it makes your > million-line query smaller. > > However, if you really want to know, write it each way, populate a db > with a million rows of fake data, and time it. > > --BenD > > Will Tomlinson wrote: > > MySQL 5. > > > > Which would be the better choice for a report table. I'm concerned and > want the query to run as fast as possible because there could end up being > millions of records in it. > > > > I could add the number of fields I need (not worried about the > flattening of this table and lack of normalization). Instructor1, > instructor2, 3, 4, etc. > > > > Then query like this: > > > > AND (e2.instructor1id = > > <cfqueryparam value="#ARGUMENTS.instructor#"> > > OR e2.instructor2id = > > <cfqueryparam value="#ARGUMENTS.instructor#"> > > OR e2.instructor3id = > > <cfqueryparam value="#ARGUMENTS.instructor#"> > > ) > > > > OR ... I could easily create a list of values, then use the IN keyword: > > > > AND someotherfield IN (<cfqueryparam value="#ARGUMENTS.somevaluelist#" > list="yes">) > > > > Thanks, > > Will > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
