[AccessD] xyz* faster than *asd

Too bad you got this silly answer in your way back SQL class. Because
it's quite obvious why a LIKE xyz* will be faster than LIKE *xyz on an
indexed field. For a LIKE *xyz the query engine have to investigate each
and every record, because it just doesn't know what leading characters to
seach - and this traversing all records is what's called a "table scan".
For LIKE xyz* the query engine can use the index, because it know that
the leading characters are "xyz". So having an index on the search field
will allways make a LIKE *xyz query by far faster than a LIKE xyz* query.
Asger

----- Original meddelelse -----

> Fra: John Bartow <john at winhaven.net>
> Til: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Dato: Fre, 08. jul 2011 23:38
> Emne: Re: [AccessD] xyz* faster than *asd
> 
> I remember asking a similar question in an SQL queries class about 20
> years
> ago. I believe the answer was "because".
> 
> Its Friday ;o)
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, July 08, 2011 12:25 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] xyz* faster than *asd
> 
> Does anyone know of a reason that LIKE is faster with the * in back
> instead
> of the front of a string to search by in the where clause?
> 
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com