Bud wrote: > On 3/29/02, Tony Schreiber penned: > >>While I know that like is always slower, consider this: >> >>WHERE left(description,1) = 'A' >> >>OR >> >>WHERE description LIKE 'A%' >> >>Which would be faster? > > > The first one would be faster. But only because it wouldn't return > any records. ;)
Apart from the fact that it does return records in some databases, the amount of records returned is absolutely irrelevant in most databases (when the number isn't huge). What matters is the type of query execution plan you get (index scan vs. full table scan) vs. the best possible plan and the amount of calculations that are required for each step. What you need to ask yourself in optimizing queries is what would be the most efficient way for the database to handle the query. Would that be an index scan or a full table scan. Can I create the circumstances in which an index scan is faster? Can I create the circumstances in which a table scan is faster? For instance, with long rows or a clustered table an index scan is usually faster because it requires less (expensive) trips to the harddisk to get the data. But if the rows you want to get are so well dispersed through the table and are so short that when reading only the blocks you want the entire table is read anyway a full table scan is faster. Unless ofcourse you have created an index on left(description,1) in which case the left function doesn't have to be executed at each row and the index becomes faster again. Reading up on the performance tuning docs of your database is an absolute necessity to answer these questions. There is no one-line answer to the question that was asked the way it was asked. Jochem ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
