Re: difficult select problem

Andrew Ballard wrote: > On Thu, Apr 9, 2009 at 1:39 PM, PJ <af.gourmet@videotron.ca> wrote: > [snip] > >> Now the silly questions = often I am curious what effect the differences >> in code will have on performance. >> For instance, why are you using author.last_name LIKE '{$Auth}%' rather >> than LEFT('$Auth') ? As I have mentioned, it is not important what >> follows the A >> > > The query optimizer can use an index on author.last_name to evaluate > "WHERE author.last_name LIKE '{$Auth}%' ". It simply seeks/scans the > index since the last_name values are ordered, and starts returning > values when it reaches the first row that begins with $Auth and stops > returning rows when it finds a value is greater than the pattern. > > To evaluate "WHERE LEFT('$Auth', LENGTH('$Auth')) = '$Auth' > ", the > server has to calculate a substring of last_name for every row in the > table and then compare those substrings to the value $Auth. > > Andrew > > Thanks Andrew. Just what I wanted to know. ;-) -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php