Composite index query performance against multiple single indexes compared to full-text index

Question

Background: I have a table with 5 million address records that I would like to find for different fields (customer name, contact name, zip code, city, phone, ...), up to 8 fields. The data is fairly stable, with a maximum of 50 changes per day, so it is almost read-only access.

The user should not tell me in advance what he is looking for, and I also want to support combined search (AND-concatenation of search terms). For example, “lincoln + lond” should search for all records containing both search terms in any of the search fields, as well as those records starting with any of the terms (for example, “London” in this example).

Problem: Now I need to select an indexing strategy for this lookup table. (As a side note: I'm trying to achieve a second second response time, the worst response time should be 2 seconds.) Which is better in terms of performance:

• Make a combined index of all the requested columns (2 of them will be required if the index limit of 900 bytes is reached)
• Put individual indexes in each of the query columns
• Make full-text index in query columns and use full-text query

I drop point 1 because it has no advantage (index usage will be limited and there will be no “index search" because not all fields fit into the same index).

: , , ? ?

Answer 1

:

" ". , . , . 2-3 , ( 3GB Ram ).

, ( ).

Answer 2

, . , .

Answer 3

FTS, . , . (, x y. x NEAR y ..).

FTS, → sql2000.

. FTS , .

Sql2008 ... .

, . , Sql2008 ( ) FTS? ?

sql2000 → sql2008 , .

, M.S.I. .

Answer 4

Grauenwolf, . , :

SELECT field1, field2, field3
FROM table
WHERE field1 LIKE '%value%

1, . , 1 ( ). "", , .