>Putting 20 fields into an index will almost always force a full index scan and is usually not the fastest way to do things. One way of handling such thing is to create a summary varchar(max) field where all the searchable field values are stored. Then use a full text index to search it. You can use a trigger to update the summary field upon insert or update.
The maximum is 16. When I tried that, most of my queries were using it. However, I found that it was better to break the SQL command in two. Thus, one SQL command to select only the primary keys with the WHERE clauses. Then, another SQL command to collect all the fields, with the INNER JOINs, where the WHERE clause is built from the primary keys. As I can only have 100 maximum, this has proven to work exceptionnally well so far in tests.
>Otherwise it is wise to look at the execution plan which index seeks are consuming the most I/O. the LIKE function will force a full index scan if ? or % are used at the beginning of the argument. Its better to avoid that.
Yes, I have been spending a lot of times analyzing this. The LIKE is something that affects it. The CONTAINS is also another one as this will always trigger one component more in the execution plan for the full text index.
>There might be other considerations as well. In our search, we search in three steps:
>1. Look for exact matching values in indexes where the index values are unique or almost so. This is extremely fast.
>2. Look for matching values in indexes with lower selectivity. This is somewhat slower
>3. Use LIKE '%MyValue%' or CONTAINS(..) in indexes. This will take longer to execute
We cannot have exact match. This is our most biggest challenge as we have half of those fields to be character type. That is the goal of offering the advanced search. If users would know the precise item number they would be looking for, they would simply enter it as is on top right in the textbox and hit search to go directly into the detail. So, when they use the advanced search, they can enter only a partial value of that field, combined with potentially one or many other fields. If only one result is found, they are redirected into the detail. Otherwise, we offer a list and they can go from there or precise their search.
