Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how to index for multi-field searches on 15m rows

how to index for multi-field searches on 15m rows

how to index for multi-field searches on 15m rows

I have a 15 million row table that includes the first middle and last names of the person and their parents.  The structure and keys are listed below.

The question is what should be indexed and  which kind of index, primary, index, all in one index or what to allow fast searches on any significant field or combination of fields.

It works really fast on Last_Name but really bad on other fields.

Any help or pointers to sources of information would be appreciated.

I have looked at the manuals chap 12 and various faq on performance etc.

table births

Field               Type      Key
Last_Name           char(21)  MUL
First_Name          char(16)  MUL
Middle_Name         char(16)
Name_Suffix         char(4)
Birth_Year          char(4)
Birth_Month         char(2)
Birth_Day           char(2)
Birth_County        char(20)
Sex                 char(1)
Mothers_Last_Name   char(21)
Mothers_First_Name  char(16)
Mothers_Middle_Name char(16)
Fathers_Last_Name   char(21)
Fathers_First_Name  char(16)
Fathers_Middle_Name char(16)
Fathers_Suffix_Name char(4)
Keyname    Unique   Field  
 lname      No    Last_Name
 lname      No    First_Name
 lname      No    Middle_Name
 lname      No    Name_Suffix
 lname      No    Birth_Day
 First_Name No  First_Name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close