Обсуждение: on indexing.

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.

such as.
If i
  SELECT * FROM house WHERE lname LIKE 'HU%';
the query planner uses and indexed search, but if i
  SELECT * from house where zip+4 like '08035%';
the query planner suggests a sequential search.

What is the threshold, is there a threshold when selecting on the zip+4
would become feasible for an index scan?

The output from the explains is as follows:
 EXPLAIN SELECT * FROM house WHERE lname LIKE 'HU%';
Index scan using h_lname_zip_key on house (cost 0.00..2313.05 rows 96
width=121)

 EXPLAIN SELECT * FROM house WHERE zip LIKE '08035%';
Seq Scan on house (cost 0.00..14135.48 rows=17 width=121)

Thanks in advance.
.jtp

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@svana.org>
http://svana.org/kleptog/
- Artificial Intelligence is the science of making computers that behave
- like the ones in the movies.