Speedy Search Question

[Brit]

If you are doing a search against several columns in the database (product category, product description, product short description) and using 'LIKE', is it faster to search each field or a concatenation of all the fields?

For example
select ...
from ...
where columnA like '%abc%'
or columnB like '%abc%'
or columnC like '%abc%'

versus

select ...
from ...
where (columnA + coulumnB + columnC) like '%abc%'

My guess is the conactenation takes too much time

 

[karluk]

I think you're right that the concatenation would add time to the query. However there's a more serious problem - the two queries aren't identical. The concatenation search will match all rows returned by the first query, but will also produce spurious matches. For example, suppose

columnA = 'scarab'
columnB = 'caliph'
columnC = 'delphi'

Then columnA + columnB + columnC = scarabcaliphdelphi'

and that matches "like '%abc%'" because of the 'ab' at the end of scarab being concatenated right before the 'c' in caliph.

 

[Brit]

good point. thanks