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 of column '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 reversed version 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, I was 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
