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
