----- Original Message ----- From: "Peter Dunham" <[EMAIL PROTECTED]> To: "MYSQL" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 12:03 PM Subject: MATCH vs LIKE
> I am trying to speed up keyword searches using MATCH AGAINST instead of > like, but am getting unexpected results. > > I had assumed that using 'WHERE MATCH fulltext(column.name) AGAINST > 'searchterm' would produce the same results as 'WHERE column.name LIKE > '%searchterm%' but it doesn't seem to. Not quite, not yet. If you search on "house" with LIKE, you will find "house", "inhouse", "houses", etc. With MATCH you will only find "house" A newer version of MySQL (4.0 or 4.1) will make you able to find "houses" (aka. LIKE 'house%'), but I gueds that "inhouse" (aka. LIKE '%house') will never be available. The reason is, that your FULLTEXT index is made up of an (alphabetically) ordered list of words. When searching, this list is traversed as a binary search. This will only be possible, if you know the starting characters (ie. 'house' and 'house%'). '%house' has a wildcard as the starting characters, therefor only a linear search would be possible. This would not do well for performance. - Carsten --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php