Do u often find your self needing to do 'dirty' SQL LIKEs like the following;
WHERE upper(member_name) LIKE 'JAY%' OR
upper(member_county) LIKE 'NAI%' OR
upper(staff_no) LIKE 'S01%' OR
upper(mobile_no) LIKE '072%' OR 'i am tired of this stuff and this is only one permutation assuming the first 3 characters hav been filled...ouuuch'
? (yes question mark... this is where the question ends!!!)
If you answer is yes OR 'Y%' ;-) ;-) then you are ready for FULL TEXT SEARCH
Make your SQL VIEWS read for FTS by always having a column called 'ts_doc' that should contain the 'searchable' columns(of ur choice) ??????????
CREATE OR REPLACE VIEW vw_members AS
SELECT members.member_id, members.member_name, member.staff_no, members.mobile_no, andanyothercolumnyouwhishcoziassumeuknowsql,
to_tsvector(COALESCE(members.member_name,'')||' '||COALESCE(member.staff_no,'') ||' '|| COALESCE(members.mobile_no,'')) as ts_doc
So that in your queries just become
WHERE ts_doc @@ to_tsquery(' searchQuery:*') ";
NB: searchQuery is nothing but a cleanup to create a valid FTS query:-
searchQuery = searchQuery.replace(" ",":* & ") - in java, but of course u can use ur prefered technique... php, sql,
With the above u can use more than one search text(in whatever order or case) to narrow down the result..
for example with searchQuery like 'mik DS1 nai' it will search in all the columns (defined in ts_doc) for words starting with mik or DS1 or nai... thereby matching all entries for nairobi, all names starting with mik eg mike,mikel etc etc
NB: approach is 'begins with....'
(those who do LIKE '%xyz%' look for another evangelist !!!!)
example is in postgres