[EMAIL PROTECTED] wrote:
Description:
Boolean mode fulltext searching returns zero hits for valid queries.
How-To-Repeat:
Create a database with a Text column. Add a fulltext index on it. Try to search for multiple words with AND or phrase syntax.
Here are examples:
mysql> select Notice_ID from Notices where match (Text) against ('+pollution +control' in boolean mode); Empty set (0.00 sec)
mysql> select Notice_ID from Notices where match (Text) against ('"pollution control"' in boolean mode); Empty set (0.02 sec)
mysql> select Notice_ID from Notices where Text like '%pollution control%'; +-----------+
| Notice_ID |
+-----------+
| 192090 |
+-----------+
1 row in set (5.00 sec)
Your LIKE query is not equivalent to your MATCH AGAINST. For example, Text containing "pollution controls" would match LIKE '%pollution control%', but would not MATCH AGAINST ('+pollution +control'...). Have you looked at Text for this row to be sure it contains exactly "pollution" and "control"?
mysql> select Notice_ID from Notices where match (Text) against ('+pollution +air' in boolean mode); Empty set (0.03 sec)
mysql> select Notice_ID from Notices where match (Text) against ('"air pollution"' in boolean mode); Empty set (0.00 sec)
mysql> select Notice_ID from Notices where Text like '%air pollution%'; +-----------+ | Notice_ID | +-----------+ | 196349 | | 196569 | | 188183 | | 192090 | | 192686 | | 199283 | +-----------+ 6 rows in set (0.17 sec)
(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
Are you certain that air is indexed? What does
SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('air');
return?
All OR searches work perfectly fine, as per:
mysql> select count(*) from Notices where match (Text) against ('air pollution'); +----------+ | count(*) | +----------+ | 100 | +----------+ 1 row in set (0.03 sec)
How many do you get with
SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('pollution');
If air isn't indexed, I'd expect 0 hits for 'air' and 100 hits for 'pollution'.
These are not overly common words:
mysql> select count(*) from Notices; +----------+ | count(*) | +----------+ | 11990 | +----------+ 1 row in set (0.00 sec)
Fix:
Use a WHERE text-column LIKE "%phrase%" for phrase searching. No known workaround for AND searches.
Submitter-Id: <submitter ID> Originator: Joe Rhett Organization:
Isite Services, Inc.
MySQL support: none Synopsis: Boolean mode fulltext searching fails. Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.16 (Source distribution)
C compiler: 2.95.3 C++ compiler: 2.95.3 Environment:
System: SunOS web031 5.8 Generic_108529-23 i86pc i386 i86pc Architecture: i86pc
Some paths: /usr/bin/perl
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC: lrwxrwxrwx 1 root root 11 Sep 15 18:17 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 root bin 956112 Jul 29 20:10 /lib/libc.so.1
lrwxrwxrwx 1 root root 11 Sep 15 18:17 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 root bin 956112 Jul 29 20:10 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/opt/mysql' '--localstatedir=/var/mysql' '--without-debug'
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
