Re: on indexing.

On Mon, Jul 02, 2001 at 02:32:37PM -0400, jtp wrote:
>
> hello list,
> since indexing seems to be a few current threads, a quick question for
> those in the mindset of dealing with them.
>
> i have a multiple index on a customer record table.
>
> table a:
> id #, first name, last name, zip+4, adress, etc.
>
> i have and index across name and zip+4 and last name for the benefit (read
> laziness) of data entry personnel. last name is a varchar(15) and zip+4
> is a varchar(10).
> if i do a select incorperating both fields it uses an index scan no
> problem, but when i do a select using only a portion of a field i get
> varying results.

If I'm reading correctly here, you have one index covering all three fields.
If this is the case then the only times the index while be used is if you
match on name, name and zip+4 or all three.

If you want searches on zip+4 to be scan if only the zip code is entered,
you need to make another index on only that field.

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
- Artificial Intelligence is the science of making computers that behave
- like the ones in the movies.