[AccessD] xyz* faster than *asd

And for the case where you have 'LIKE *FOOBAR', that will be slower because the db has to first figure out how long the data is, then look at the last n characters to check for a match, whereas LIKE FOOBAR* still only has to look at the first n characters.

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Friday, July 08, 2011 1:56 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] xyz* faster than *asd

If by 'back' you mean on the right and 'front' meaning on the left, then the answer is surely to do with how the pattern matching takes place. With a search string like "FOOBAR*" the Db engine can simply filter all records where the field starts with 'FOOBAR', and that will be fairly fast as only the first n characters in each record need to be examined.

But with LIKE *FOOBAR* the starting location of the search string is indeterminate. Therefore for each record, more characters have to be examined to check if the substring 'FOOBAR' is in the data. For a false match that means every character (minus n - the length of the substring) in the string needs to be checked.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, July 08, 2011 1: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