Hi Im having problems coming up with an effective search on a table which stores company details..
I need to be able to allow people to type a search which checks against two of the fields in the table (company name and a keywords field)
At the moment I'm having to use the following..
SELECT Company_Name FROM table WHERE (
Company_Name LIKE 'dow' OR
Company_Name LIKE 'dow %' OR
Company_Name LIKE '% dow' OR
Company_Name LIKE '% dow %' OR
Keywords LIKE 'dow' OR
Keywords LIKE 'dow %' OR
Keywords LIKE '% dow' OR
Keywords LIKE '% dow %')
ORDER BY Company_Name LIMIT 200;
Not very tidy I know but I need to be able to find a word which may occur in one of the 2 fields but not find for example 'dow' in 'window' which would happen if I used LIKE '%dow%'.
This seems to be slower than I'd like especially when the user types in multiple words and the search is multiplied by the number of words they want to search on.
So I though of using MATCH AGAINST so I could use...
SELECT Company_Name FROM table WHERE MATCH(Company_Name, Keywords) AGAINST('dow');
The problem with this is firstly the keywords field is of data type text which I think may be a problem as the FULL TEXT index appears to have been created but will not work on this field (is it possible to a FULL TEXT INDEX on a text data type field ??)
It does however work on the company_name field but the second problem is that words searched that are 3 characters or less are ignored so 'dow' would return no results.
I am running MySQL version 3.23.54 so I don't think I am able to change the 3 character rule in this version and cannot use IN BOOLEAN MODE either.
Any suggestions on how I could perform this type of search any other way or how I could refine what I am already doing would be realy helpful.
thanks to anyone who manages to give me some advice.....