Re: difficult select problem
| From: | Andrew Ballard | Date: | Thu, 09 Apr 2009 18:08:01 +0000 |
| Subject: | Re: difficult select problem | ||
| References: | 1 2 3 4 5 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-291310@lists.php.net to get a copy of this message | ||
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