filters and indexes

Thread View: borland.public.delphi.database.win32

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Paul Dolen Fri, 14 Dec 2007 03:13

#130

Author: Paul Dolen
Date: Fri, 14 Dec 2007 03:13

17 lines
1586 bytes

If I was a real database expert, I'd probably know the answer.  But,
I'm not, so, I figured I'd ask for some general guidelines.

Basically, I want to filter based on up to three different fields. Say
I want to filter based on gender, last name, and/or zip code.
Something like:

(gender = 'Male') and (LastName >= 'D') and (LastName < 'E') and
(ZipCode >= 70000) and (ZipCode < 80000) 

Well, I know I could create a composite index, based on all three of
those fields.  But, would such a composite index be helpful to a
database engine to do the
filtering?  I'm thinking probably not.  I could create three different
indexes, one on each field.  Would the filter be able to make use of
the different indexes simultaneously?  Or, do I just need to pick one
index, and have it table scan to match the other filter criteria?

Re: filters and indexes

#131

Author: Zoren Lendry
Date: Fri, 14 Dec 2007 03:49

34 lines
2662 bytes

Paul Dolen wrote:
> Basically, I want to filter based on up to three different fields. Say
> I want to filter based on gender, last name, and/or zip code.
> Something like:
> (gender = 'Male') and (LastName >= 'D') and (LastName < 'E') and
> (ZipCode >= 70000) and (ZipCode < 80000) 
> 
> Well, I know I could create a composite index, based on all three of
> those fields.  But, would such a composite index be helpful to a
> database engine to do the
> filtering?  I'm thinking probably not.  I could create three different
> indexes, one on each field.  Would the filter be able to make use of
> the different indexes simultaneously?

Whoa, slow down thar, pardner. Help us help you:
1) What database? What connectivity components?
2) Are you filtering client-side, after you have selected the data via 
SQL? Or are you talking about whether your SQL statement will use an index?

Composite indexes probably will not help you. Indexes are for WHERE 
clauses much more so than for ORDER BY clauses. So indexes on those 
various fields individually will likely be very beneficial, except in 
cases like your (gender = 'Male'), because of the low selectivity 
[meaning that field only contains a few values: Male, Female, Confused, 
Unknown]. Indexes can actually hurt sometimes rather than help when you 
have many records with very few values.

Also, your "(LastName >= 'D') and (LastName < 'E')" can be expressed by 
a "LastName LIKE 'D%'. You'll have to test to see which expression 
results in the optimal index utilization, by reference to the plan.

So, provide some more details and you'll get a better answer.

Loren

Re: filters and indexes

#132

Author: Tiedo Kruisselbrink
Date: Fri, 14 Dec 2007 07:45

14 lines
1261 bytes

Zoren Lendry wrote:

> Paul Dolen wrote:
> > ...
>
> Whoa, slow down thar, pardner. Help us help you:
> 1) What database? What connectivity components?
> 2) Are you filtering client-side, after you have selected the data
> via SQL? Or are you talking about whether your SQL statement will use
> an index?

3) What Delphi version?

Tiedo

Re: filters and indexes

#133

Author: Paul Dolen
Date: Fri, 14 Dec 2007 16:11

9 lines
1217 bytes

I'm using TClientDataSet for a local memory table, no external server.
Delphi 2007.

>> 1) What database? What connectivity components?
>> 2) Are you filtering client-side, after you have selected the data
>> via SQL? Or are you talking about whether your SQL statement will use
>> an index?
>
>3) What Delphi version?

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads