DB Index and concatenated fields
-
What sort of index should I be looking at if I want to search a DB in the following manner:
@code said:
DECLARE @input varchar(255)
SET @input = ? -- Stored procedure. Will often pass in 'a%'SELECT *
FROM e_clients
WHERE
first_name like @input
OR last_name like @input
OR (first_name + ' ' + last_name like @input) -- This is the hitchAnd index on first_name and an index on last_name is simple enough. But what should I do for the "full name" search?
To clarify,
If I enter [b]"L"[/b] I should get [b]L[/b]orne Kates
If I enter [b]"K"[/b] I should get Lorne [b]K[/b]ates
If I enter [b]"Lorne K"[/b] I should get [b]Lorne K[/b]ates
-
If you don't care about indexing (a table scan isn't that bad if there aren't a lot of rows and not a lot of searching), then the approach you took is fine.
Otherwise, you may want to consider full-text indexing, as it will automatically handle situations like this (though your queries will become different). There are some other options too, but those also involve some table changes, etc.
A simple thing to do would be to split input on ' ', and have @input_first and @input_last. Your query would then become
set @input_first = (everything to the left of the first space)
set @input_last = (everything to the right of the first space, or if null then @input_first)
SELECT *
FROM e_clients
WHERE
first_name like @input_first
OR last_name like @input_last
-
Looks like there's about 2000 records in the table, so that isn't so bad.
One of the design features is a single textbox. I'm doing a Google-esque "search as you type" ajax-thing for clients by name. I could do all sorts of funky string maniuplation based on spaces, I suppose.
I'll field test the current approach, and read up on full-text indexing.
Thanks, Alex.
@Alex Papadimoulis said:
If you don't care about indexing (a table scan isn't that bad if there aren't a lot of rows and not a lot of searching), then the approach you took is fine.
Otherwise, you may want to consider full-text indexing, as it will automatically handle situations like this (though your queries will become different). There are some other options too, but those also involve some table changes, etc.
A simple thing to do would be to split input on ' ', and have @input_first and @input_last. Your query would then become
set @input_first = (everything to the left of the first space)
set @input_last = (everything to the right of the first space, or if null then @input_first)
SELECT *
FROM e_clients
WHERE
first_name like @input_first
OR last_name like @input_last
-
In Oracle you can create a function-based index for this:
create index client_name_idx on e_clients(first_name || ' ' || last_name);
I don't know if your database supports something similar.
