SQL Server's like '%xxx% ' optimizations, full-text indexing

20 million rows of data table, first of the Address field to do '%xxx% ' fuzzy query

This is the estimated query plan

This is the estimated actual query results, took 37 seconds to complete the query

Or the previous data, but this time using ' xxx% ' to do the query, and now do not index

Query speed of 10 seconds, still do a full table scan

The next one is not a fuzzy query, direct =, query a step more "parallelism"

Three seconds to complete the query, is also very slow, should have gone all the table scan

Now create a normal index for the address field

After the normal index is built, try to find '%xxx% ', from the query plan, '%xxx% ' is not available to the normal index

Sure enough, the query is time-consuming and not indexed as before, basically unchanged

Now try to query ' xxx% ', according to the query plan can see, this query can walk just the normal index we built

The result of the query is 4 seconds, and the query result is 10 seconds before the index is indexed.

Next, Direct = lookup, you can see the use of the index

The query took 0 seconds to the millisecond level, and from this point, it can be seen that the normal nonclustered indexes support the direct match (=) query with the best, then the like ' xxxx% ', and the like '%xxx% ' does not support

Then we build a full-text index on the Address field

The following is the usage syntax for full-text indexing, and the query process

After the full-text index is added, the query time is 2 seconds, or a little slow, and then tested several times, usually in a second or so

In addition, SQLServer2008 's full-text index does not seem to be completed immediately, but need to wait in the background for a period of time to fully establish, in this period of time the results of the query returned is not the same.

For example, after two queries, the result is more than the previous one, the full-text index is being established, and finally there is a stable state.

SQL Server's like '%xxx% ' optimizations, full-text indexing