Dear listmembers
My problems are: jokers appear not to work as expected in boolean
fulltext search and I can't figure out how to order results according to
any relevance. I need someone to point me into the right direction.
I run MySQL 4.0.1:
> mysqladmin version
mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.1-alpha
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 56 days 2 hours 14 min 38 sec
My table contains some data:
-rw-rw---- 1 mysql daemon 187621564 Jan 8 20:02 plaintext.MYD
-rw-rw---- 1 mysql daemon 92874752 Jan 8 20:04 plaintext.MYI
-rw-rw---- 1 mysql daemon 8648 Jan 8 20:02 plaintext.frm
mysql> select count(id) from plaintext;
+-----------+
| count(id) |
+-----------+
| 14579 |
+-----------+
1 row in set (0.02 sec)
and looks like this:
mysql> describe plaintext;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| doc | varchar(16) | | MUL | | |
| code | int(10) unsigned | | MUL | 0 | |
| part | tinyint(4) | | | 0 | |
| bgetxt | text | YES | MUL | NULL | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
which was created this way:
CREATE TABLE plaintext (
id int(11) NOT NULL auto_increment,
doc varchar(16) NOT NULL default '',
code int(10) unsigned NOT NULL default '0',
part tinyint(4) NOT NULL default '0',
bgetxt text,
PRIMARY KEY (id),
KEY doc (doc),
KEY code (code),
FULLTEXT KEY bgetxt (bgetxt)
) TYPE=MyISAM;
Data: The word 'Placidus' appears 9 times in document '122 III 150' and
just once in document '84 II 304' and in no other document at all.
This works (not in boolean mode):
mysql> SELECT doc FROM plaintext WHERE MATCH(bgetxt) AGAINST('placidus');
+-------------+
| doc |
+-------------+
| 122 III 150 |
| 84 II 304 |
+-------------+
2 rows in set (0.00 sec)
This works too:
mysql> SELECT doc FROM plaintext WHERE MATCH(bgetxt) AGAINST('placidus' IN
BOOLEAN MODE);
+-------------+
| doc |
+-------------+
| 122 III 150 |
| 84 II 304 |
+-------------+
2 rows in set (0.00 sec)
...and this boolean fulltext search correctly returns just one row:
mysql> SELECT doc FROM plaintext WHERE MATCH(bgetxt) AGAINST('+placidus
+christina' IN BOOLEAN MODE);
+-------------+
| doc |
+-------------+
| 122 III 150 |
+-------------+
1 row in set (0.00 sec)
According to the Very Fine Manual I would expect that the following query
would return at least 2 rows (remember: the word 'placidus' appears in two
of the documents):
mysql> SELECT doc FROM plaintext WHERE MATCH(bgetxt) AGAINST('placi*' IN
BOOLEAN MODE);
Empty set (0.00 sec)
What happens? As it appears, the *asterisk* joker is not supported. And I
can't see from the manual, how to obtain some relevance data when using
the modifier 'IN BOOLEAN MODE'. I tried this:
mysql> SELECT doc, MATCH(bgetxt) AGAINST('placidus' IN BOOLEAN MODE) AS
score FROM plaintext WHERE MATCH(bgetxt) AGAINST('placidus' IN BOOLEAN
MODE);
+-------------+-------+
| doc | score |
+-------------+-------+
| 122 III 150 | 1 |
| 84 II 304 | 1 |
+-------------+-------+
2 rows in set (0.00 sec)
I need to sort rows returned by a boolean search in some order of
relevance. Any enlightenment would be very helpful. TIA
Thomas Spahni
---------------------------------------------------------------------
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
