Hi,
We are in the process of creating a new data model for the Gift Registry which my company is planning to have a release in next year.
Would like to get some inputs / thoughts on bellow.

1. In our current data model we keep all the names a separate table where we do the search. The user has to enter Two char for the first name and two char for the last name. The select query which we use is wild char searching ( we use "Like" to ensure we retrieve all the matching criteria). We have created two more fields called first name search code & last name code which are char type and are indexed for optimization. Going forward Does this look good?. Any other optimization tech. I can use. I heared about the db2 v8 wild char indexing for a varchar. Does that really helpful?.

Thanks for your inputs!.

Anto.

Are you asking the user to input the first two characters on the name, or any two characters of the name? If it is the first two characters, then you should not have any performance problems with LIKE :name (when :name = 'XX%').

Personally I would make them enter in the first 3 characters of the last name, with first name optional. The other thing that could be done is a Soundex search.

Yes, we ask only the first two char of the User's "First Name" and the last two char's of the users "Last Name". As most of the gift registries today we have built it the same way ( my business user's View!!). Like I said before we have created two more fields for the indexing purpose ( first name search code / last name search code), which some how helps with performance. Does any other optimization tech which would be better than this?

What is soundex search?

thanks

I was thinking you would just use the last_name and first_name columns and index those instead creating new columns with just 2 characters. However, if you create a composite index with the 2 character last_name and 2 character first (one index) then it should be the clustering index.

Soundex allows one to do a fuzzy search on a name, so exact spelling is not required. Do a google search.

Thanks Marcus. I think I can remove the 2 char field.
I was reading some article about a wild char indexing in db2 v8.1 which is helpful in this scenario of searching ( Searching with "Like %'FRST_NAME'%). Any pracitical exposure?
Also I am thinking of partitioning that table also.

'NAME%' will run much faster than '%NAME%".

thanks Marcus.
But my question is , assume that you have a table which has first name, last name and type_code which has 10 million rows. The user may enter only two char's for the first name text and 2 char's for the last name char.
The select query looks like this,

select first_name,last_name from table_name where first_name like 'first name%' and last_name like 'last name%'

These two fields are indexed. Is it good to create a covering index or normal index for this?.

Does indexing the type_code and adding that into the where clause will help my query optimized?. So Does the above query is fast or,

select first_name,last_name from table_name where first_name like 'first name%' and last_name like 'last name%' and type_code = 'IN'

is fast?.

What extra I can do to make this search to the maximum optimized.

Thank you very much. I really need something broder which I can include.

Thanks!.