Menu
I have a situation where I would like to search a single word.
For that scenario, which query would be good from a performance point of view?
Select Col1, Col2 from Table Where Col1 Like '%Search%' or
Select Col1, Col2 from Table Where Col1 CONTAINS(Col1,'Search') ?
822
Assuming all that is correct, I'll go ahead and make the following statement: The fulltext index is in fact an index, which makes search faster. It is large, and has fewer search posibilities than LIKE would have, but it's way faster.
Thus it is clear that CONTAINS is faster than LIKE.
1 Answer. Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string.
you can absolutely write a sub-query that performs horribly, does horrible things, runs badly, and therefore absolutely screws up your system. just as you can with any kind of query. i am addressing the bad advice that a sub-query is to be avoided because they will inherently lead to poor performance.
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.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
