[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