Basic Question: In text fields, is prefix matching significantly faster than suffix matching?
Background:
I'm designing a database schema where a common operation will be "search for substring x either at the beginning or end
ofcolumn 'str'".
1. I could have the client issue...
SELECT * FROM tbl WHERE str LIKE 'x%' OR str LIKE '%x'
2. Alternatively, I could store column 'rev_str' as a reversed version of column 'str' and have the client produce a
reversedversion of x on each query (call it r). Then the client would issue...
SELECT * FROM tbl WHERE str LIKE 'x%' OR rev_str LIKE 'r%'
...which would use prefix matches only instead of requiring suffix matches. Since I've seen this form used by others,
Iwas wondering if it's necessary - i.e. if databases really do perform prefix matching faster?
3. Is there a solution I'm unaware of with even better performance?
Thanks,
Andrew
