Hi everybody,
In the Names table we have cl_Name C(25), cf_Name C(20), cM_Initial C(1) fields. If I want to search by last name and first name I can use:
SET ANSI OFF
select ... from ... where cl_name ="GREE" and cf_name = "MAR" and cM_Initial = ""
or I can use
lcName = padr("GREE",25,"_") + padr("MAR",20,"_")+padr("",1,"_")
select ... from ... where cl_name + cf_name + cm_Initial LIKE lcName
I currently put the following indexes in my local copy of Names table:
cl_name
cf_name
cm_initial
cl_name+cf_name+cm_initial
Today I re-coded from the first approach to the second and it seems to me that I slowed down my searches considerably.
I think, in either case we do not need to have an index on cm_Initial (it looks stupid to have an index on 1 char field).
What do you think? I would appreciate your input.
I've done the same with the Phone (we have separate fields for different phone parts). I haven't tested this change yet.
Thanks in advance.
If it's not broken, fix it until it is.
My Blog