RE: Phrase based fulltext searching
A relatively straightforward way round this - and a number of other problems
with FULLTEXT - is to combine a full text search with a LIKE criterion to
narrow the results. Example (assuming that you are searching a table called
Documents on a TEXT field called Term):
SELECT * FROM Documents WHERE MATCH (Term) AGAINST ('search phrase') AND
Term LIKE '%search phrase%'
This, in my experience, appears to be nearly as fast (in perceived terms) as
the straight full text search because the LIKE criterion, whilst slow on
large databases if used with a % wildcard at the beginning (which prevents
use of any conventional indexes), is only applied here on the set matching
the fulltext criterion. It should therefore only be really slow in extreme
conditions where the number of entries matching the fulltext criterion is
very high. Make sure you write the query in this order, though - I would by
no means guarantee that MySQL will optimise it if you write:
SELECT * FROM Documents WHERE Term LIKE '%search phrase%' AND MATCH (Term)
AGAINST ('search phrase')
If optimisation does not take place, this query could easily be several
orders of magnitude slower than the correctly sequenced version!
Roger Bennett
-
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
RE: Phrase based fulltext searching
I thought I read that was in the plan for 4.0. I can't find where that's stated in the docs though - all I see is the stuff about boolean operators. Does anyone have experience with using the AOL PLS package for text searching? I am thinking of using it. Braxton -Original Message- From: Philip Mak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 5:39 PM To: [EMAIL PROTECTED] Subject: Phrase based fulltext searching I read through the MySQL documentation on full text indexing, and there does not seem to be a way to search for a *phrase*, e.g. searching for a document that contains "Sailor Moon", as opposed to one that contains the word "Sailor" and the word "Moon", not necessarily together. (Unless I use LIKE "%Sailor Moon%", but that's inefficient...) Is the above statement correct? What techniques do people here use to overcome that limitation? (Using a program separate from MySQL for indexing text, perhaps?) -Philip Mak ([EMAIL PROTECTED]) - 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 - 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
