I have a table with approximately 80,000 records. I've created a full text
index on the table with the following statement:
CREATE FULLTEXT INDEX ON AdsAuto
(
StockNumber,
YearText,
Make,
Model,
Trim,
Transmission,
ExteriorColor,
Description,
Equipment,
FullTextKeywords,
VIN,
AdCode
)
KEY INDEX PK_AdsAuto ON TraderCatalog
WITH CHANGE_TRACKING AUTO
This query takes 13 seconds to run:
SELECT ID, Year, Make, Model, Description
FROM AdsAuto
WHERE CONTAINS(AdsAuto.*, '"automatic*"')
This query takes 2 seconds to run:
SELECT ID, Year, Make, Model, Description
FROM AdsAuto
WHERE CONTAINS(AdsAuto.*, '"auto*"')
Why does searching for the word "automatic" take so long to run? Any other
word takes only 2 seconds for the search and using a partial match like
"auto*" is fast. I've also tried searching the fields individually by using
this query:
SELECT ID, Year, Make, Model, Description
FROM AdsAuto
WHERE CONTAINS(AdsAuto.Transmission, '"automatic*"')
This is also very fast. Searching any of the individual fields for the word
"automatic" is just as fast, but when searching AdsAuto.*, it dogs the server.
Execution plans are the same when searching for "automatic*" vs. "auto*". I
cannot find the cause of the slow search.
>> Stay informed about: Querying the word ""automatic""