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 hitch

    And 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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.