Hello,
Using a fulltext search on a products database I'm running into some
rather strange results.
I'm trying to get a list of matches sorted by relevance.
Here is the query:
select products_name,MATCH
(products_name,products_name,products_description,item) AGAINST
('necker knife') as score from products where MATCH
(products_name,products_name,products_description,item) AGAINST
('necker knife');
I get:
+--------------------------------+------------------+
| products_name | score |
+--------------------------------+------------------+
| Necker Knife | 17.7619809439664 |
| Foam Block | 4.38341880854866 |
| Foam Block | 4.64646846860058 |
| Horn Mounting Plate | 3.32702360881095 |
| Antelope Horn Mounting Plate | 3.30728558037349 |
| Elk Horn Mounting Plate | 3.32702360881095 |
| Moose Horn Mounting Plate | 3.30728558037349 |
| Steer Horn Mounting Plate | 3.30728558037349 |
| Right Hand Fish Skinning Knife | 6.85219410155259 |
| Skife Knife | 4.00494338668156 |
| Strait Blade | 3.66732441025881 |
| Extra Wide Lip Blade | 3.68254656880296 |
| Carbide Steels | 3.8344809977662 |
+--------------------------------+------------------+
The MySQL manual (http://www.mysql.com/doc/F/u/Fulltext_Search.html)
states:
When it is used (fulltext matching) in a WHERE clause the rows
returned are automatically sorted with relevance decreasing.
As you can see, it is *not* sorting by relevance properly (a score of
6.85 is listed under 3.30).
So, trying to force it to, I try this:
select products_name,MATCH
(products_name,products_name,products_description,item) AGAINST
('necker knife') as score from products where MATCH
(products_name,products_name,products_description,item) AGAINST
('necker knife') order by score;
(added "order by score") And get this:
+--------------------------------+------------------+
| products_name | score |
+--------------------------------+------------------+
| Right Hand Fish Skinning Knife | 6.85219410155259 |
| Foam Block | 4.64646846860058 |
| Foam Block | 4.38341880854866 |
| Skife Knife | 4.00494338668156 |
| Carbide Steels | 3.8344809977662 |
| Cheeker Tool | 3.76766426959713 |
| Extra Wide Lip Blade | 3.68254656880296 |
| Strait Blade | 3.66732441025881 |
| Elk Horn Mounting Plate | 3.32702360881095 |
| Horn Mounting Plate | 3.32702360881095 |
| Antelope Horn Mounting Plate | 3.30728558037349 |
| Moose Horn Mounting Plate | 3.30728558037349 |
| Steer Horn Mounting Plate | 3.30728558037349 |
+--------------------------------+------------------+
As you can see, it now sorts by score, but has knocked off the highest
result!
I'm using MySQL 3.23.32 on a Redhat 7.1 Linux box.
Thanks in advance for any help,
Cheers,
-Jason
--
.--- .- ... --- -. / -- --- .-. . .... --- ..- ... .
Jason Morehouse
[EMAIL PROTECTED]
Netconcepts LTD
Auckland, New Zealand
Linux - keyword: uptime
---------------------------------------------------------------------
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
