LIKE, RLIKE or FULLTEXT?
I am about to build a little search engine and I was wondering if experienced people could give me some advice. I was wondering which angle I should have:
RLIKE sounds good because of the use of RegExp. But LIKE or RLIKE have both the downside of being a pain when it comes to perform a search against multiple columns. My main problem with FULLTEXT is that it doesn't allow searches for words less than three chars. Hum... what about poor accrs and abbrs? Is there a trick I don't know? Like using wildcards maybe?
thanks for your time and patience :)
give an example of the types of columns that would all be the target of a search
would you look for "toledo" in address1 or address2 or aptno or city or state or country?
or would it make more sense to search for "toledo" in city only?
what about searches on title and synopsis and teaser and fullarticle? that makes more sense
however, i will venture that there aren't too many searches where you have so many similar columns that need to be the target of a combined search
so i would start with LIKE with ORs
and consider moving to something else only when i discovered a situation that LIKE doesn't cover well
Yeah I won't search that many columns so I guess that I will start with LIKE. However, tell me if I am wrong, it would be a good idea to build an index at the beginning of the project (I am thinking long term here, I might need one day) for all the cols who might be searched.
not an index for all columns, but an index on each
even though the indexes might not be used if you are looking for something inside the column, LIKE '%something%'
yes exactly rudy :)
so now I will try to figure out how to do it (which shouldn't be a problem) and I will post the table structure just to make sure that I haven't made any mistakes ruining my work in the future.