In SQL Server (2008), I have a FullText index on two columns, call them Table1.FirstNames and Table2.LastNames. After profiling some queries, I came up with the following results:
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR CONTAINS(LastNames, 'Bob')
=> 31 197ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE (FirstNames LIKE '%Bob%') OR CONTAINS(LastNames, 'Bob')
=> 1941ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR LastNames LIKE '%Bob%'
=> 3201ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob')
=> 565ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE FirstNames LIKE '%Bob%'
=> 670ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(LastNames, 'Bob')
=> 17ms
SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE LastNames LIKE '%Bob%'
=> 3ms
This behaviour persists even if I rebuild the FullText index.
FullText is usually much faster than a LIKE query over large sets of data in a specific language, but why do query speeds slow down by an order of magnitude when I OR together two FullText clasues?