Full Text Searching (using the CONTAINS) will be faster/more efficient than using LIKE with wildcarding. Full Text Searching (FTS) includes the ability to define Full Text Indexes, which FTS can use. I don’t know why you wouldn’t define a FTS index if you intended to use the functionality.
LIKE with wildcarding on the left side (IE: LIKE '%Search'
) can not use an index (assuming one exists for the column), guaranteeing a table scan. I haven’t tested & compared, but regex has the same pitfall. To clarify, LIKE '%Search'
and LIKE '%Search%'
can not use an index; LIKE 'Search%'
can use an index.