How to index a street address column

I have a Location table with a street address column. My app provides a search mechanism that queries locations with this column and I am trying to speed up the queries. The db has a little over 2 million records and I just thought I'd put out this question 开发者_Go百科to see what people have done. I'm sure I'm not the first. My question is basically this: given a primary key, address, city, state, zip columns, how should this table be indexed so that queries like the following don't take 10 seconds:

`select * from location where loc_address_s like '%blvd%'`
`select * from location where loc_address_s like 'oak' AND loc_city like 'salem'`

etc...

Your best bet, if it's at all possible, is to break the address down into its components. This is how the U.S. post office stores addresses as well as many (most?) large address handling companies and agencies. Database design best practices usually include not having a column hold more than one piece of data for a row. Right now you're storing a street number, street direction (N for "North Main Street" for example), street name, street type, etc.

Once you have everything broken out properly you can index individual columns if you need to do that.

I'm sure that there's a standard out there (a quick search of the ansi.org website didn't give me anything though). Storing addresses is a pretty common thing to have to do.

Also, you can find many address clean-up services and software to break up addresses into these columns if you don't want to go through that effort entirely on your own.

A query that includes a LIKE predicate starting with % cannot be helped by an index - it will have to do a full table/clustered index scan.

Generally, the solution to problems such as these is full-text indexing. Take a look at this documentation for info.

However, I've implemented custom tokenisation and then using regular indexes successfully in the past too - for address data. Split the addresses up into words/tokens and store each unique token in a Token table. Then have an Address table that contains a unique identifier for each address and any other meta-data you want. And, lastly, an AddressToken table which has a foreign key to each of the previous tables and a row for each token in each address. You might want to denormalise this approach (possibly via an indexed via) to get even better performance.