Hello,
I know that this topic has been discussed many times but I'm about to change my
database structure to solve my pb so I
prefer to be sure that this is the good solution.
This is my table for the moment...
create table Test ( Key INTEGER PRIMARY KEY, Name VARCHAR(260) );
create index idx_testname on Test(Name);
insert into Test (Name) values ( 'FooD' );
As the select is case sensitive:
select Key from Test where name='FooD'; returns 1
select Key from Test where name='food'; returns null
select Key from Test where name='FOOD'; returns null
...
if I use a like comparator everything is ok
select Key from Test where name like 'FooD'; returns 1
select Key from Test where name like 'food'; returns 1
select Key from Test where name like 'FOOD'; returns 1
But this select doesn't use the Index Table and is therefore a lot slower...
I have to keep the case sensitive string for further use...
So, for me, the only solution to keep speed will be to duplicate the name field in
idxName...
create table Test ( Key INTEGER PRIMARY KEY, idxName VARCHAR(260), Name VARCHAR(260)
);
create index idx_testname on Test(idxName);
insert into Test (idxname,Name) values ('food', 'FooD' );
and then
select Key from Test where idxname=lower('Food'); returns 1
select Key from Test where idxname=lower('food'); returns 1
select Key from Test where idxname=lower('FOOD'); returns 1
So it works, but is there any better solution ?
My goal is to have maximum speed...
Perhaps by using a shorter idxname ( idxName VARCHAR(60))
and a select in a select ?
Thx for any advice...
Regards
Barx
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
