Indexing Concatenated Fields

[jmikow]

We have a situation where our clients are going to be able to browse/search through our database to find a reference number when placing an order. We currently have 2 fields, FirstName and LastName. We want the performance of the search to be as high as possible, but we are unsure if we can create an index on the two fields concatenated together. We though about creating a view, but we are running MSSQL 2000 Standard, so we can't create an index on the view. We also considered creating another column which is the two concatenated together, but that seems redundant and we'd like to do it differently if possible.

Any suggestions on how to improve the performance of searching for these fields would be great.

 

[JamesLean]

An index can be built on more than one column. In your case your searches may look something like:

SELECT <cols> FROM <table>
WHERE lastname = 'smith' AND firstname = 'john'

You could create an index over both columns:

CREATE INDEX myindex ON table_name(lastname, firstname)

This should improve searching on both fields.

Bear in mind that this will also improve searches on just a lastname, however if you just search on firstname then this index will not be able to be utilised (you could create a separate index on just the firstname column if this was an issue).

--James

 

[jmikow]

Thanks for the tip.

Using the same idea, am I thinking right that I could create one index which would consist of LastName, FirstName, City, ... and require the user to at least enter a Last name to search by. Wouldn't this then increase the performance for any search they do? Or, is there a better way to do that?

 

[JamesLean]

Designing indexes is quite a complex part of tuning a database and depends entirely on the specific database and how it's going to be used.

Indexes help with reading from the table, but can have a negative effect when performing alterations (inserts, updates, deletes).

I would probably only use multi-column indexes on combinations of columns which you know are always going to be searched together, eg firstname + lastname.

I would be inclined to then create indexes on other individual columns which might also be searched, eg city.

It may not be the best idea to just create one index on all your columns, especially if you perform a lot of inserts on this table too.

--James

 

[jmikow]

So, if I wanted to require the LastName, then I would create an index for the LastName and each of the other fields? Then SQL would use the appropriate index depending on my query?

Thanks again.