MYsql FULLTEXT query yields unexpected ranking; why?

Question

i'm trying fulltext search with tags but it doesn't works properly for me chek attached image please enter image description here

The query is:

 SELECT *, 
         MATCH(tags) AGAINST ('tag3 tag6 tag4') AS score 
    FROM items
ORDER BY score DESC

why does the score is not ordering in the right order fields? if you check the second row has all the tags i searched while the first field does not have tag3 keyword .

i mean id fields order should be : 5,1,2 .. etc and NOT 1,5,2..etc

where is my mistake?

then i would like to search first in tags field then if no results i would like to search the same keyword as FULLTEXT inside description field, so users will search both in tags and description if tags does not match , is it possible in the same query or i need two separated queries?

You make it harder for answerers when the code you're asking about is show in an image rather than as text. That means we have to retype it -- a pain in the neck. — O. Jones, Sep 10 '12 at 22:26

O. Jones · Accepted Answer · 2012-09-10T22:29:57.520

2

In this document http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html it says "For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results."

If your items table is small -- a sample table, for example -- you are probably hitting this problem and getting a "bizarre" result.

You may wish to try this query IN BOOLEAN MODE to see if your results match your prediction. Try this.

    SELECT *, 
           MATCH(tags) AGAINST ('tag3 tag6 tag4' IN BOOLEAN MODE) AS score 
      FROM items
  ORDER BY score DESC

The Boolean mode disables the word-distribution ranking. Notice that you should understand the difference between natural-language and Boolean modes, and once you have a decently sized table, make a smart choice about which one to use. If you're searching for the kind of tags that blogs have in them, Boolean may be the way to go.

edited Sep 10 '12 at 22:29

answered Sep 10 '12 at 22:23

O. Jones

103,626
17
118
172

now what if i need to search both in tags and description fields, having priority on tags field? :D – itsme Sep 10 '12 at 22:31
1

Please read the reference manual document about full-text search. It's quite a complex and sophisticated kind of search. If you need both Boolean tag-style search AND natural-language description search you're probably going to have to normalize out your tag column into a new table with one tag per row, and only use FULLTEXT for your description search. The problem with natural-language search is that it deëmphasizes very common words in the search ranking. So if you have some very common tags they might not be as effective for searching as you wish. – O. Jones Sep 10 '12 at 22:39

score 1 · Answer 2 · answered Sep 10 '12 at 22:58

First of all, here is your sample data loaded into MySQL 5.5.12 on my Windows7 Machine

mysql> DROP DATABASE IF EXISTS lspuk;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE lspuk;
Query OK, 1 row affected (0.00 sec)

mysql> USE lspuk
Database changed
mysql> CREATE TABLE items
    -> (
    ->     id int not null auto_increment,
    ->     description VARCHAR(30),
    ->     tags VARCHAR(30),
    ->     primary key (id),
    ->     FULLTEXT tags_ftndx (tags)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO items (description,tags) VALUES
    -> ('the first' ,'tag1 tag3 tag4'),
    -> ('the second','tag5 tag1 tag2'),
    -> ('the third' ,'tag5 tag1 tag9'),
    -> ('the fourth','tag5 tag6 tag2'),
    -> ('the fifth' ,'tag4 tag3 tag6'),
    -> ('the sixth' ,'tag2 tag3 tag6');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

Please look at the way the tag population is happening in MySQL:

mysql> SELECT 'tag1',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag1%' UNION
    -> SELECT 'tag2',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag2%' UNION
    -> SELECT 'tag3',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag3%' UNION
    -> SELECT 'tag4',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag4%' UNION
    -> SELECT 'tag5',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag5%' UNION
    -> SELECT 'tag6',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag6%' UNION
    -> SELECT 'tag9',COUNT(1) tag_count FROM items WHERE tags LIKE '%tag9%';
+------+-----------+
| tag1 | tag_count |
+------+-----------+
| tag1 |         3 |
| tag2 |         3 |
| tag3 |         3 |
| tag4 |         2 |
| tag5 |         3 |
| tag6 |         3 |
| tag9 |         1 |
+------+-----------+
7 rows in set (0.00 sec)

mysql>

Look carefully and please note the following facts:

Each row has exactly 3 tags
The order the tags are requested vs how many of each tag exists seems to govern the score

If you remove tag4 and run the query, you get no score at all

mysql> SELECT *,MATCH(tags) AGAINST ('tag3 tag6') as score FROM items ORDER BY score DESC;
+----+-------------+----------------+-------+
| id | description | tags           | score |
+----+-------------+----------------+-------+
|  1 | the first   | tag1 tag3 tag4 |     0 |
|  2 | the second  | tag5 tag1 tag2 |     0 |
|  3 | the third   | tag5 tag1 tag9 |     0 |
|  4 | the fourth  | tag5 tag6 tag2 |     0 |
|  5 | the fifth   | tag4 tag3 tag6 |     0 |
|  6 | the sixth   | tag2 tag3 tag6 |     0 |
+----+-------------+----------------+-------+
6 rows in set (0.00 sec)

The evaluation method seems to be based on the average number of tokens field and presence and/or absence of specific values in a particular order affects scoring. If you apply different styles of scoring and tag specification, note the various scores:

mysql> SELECT *,MATCH(tags) AGAINST ('tag3 tag6 tag4') as score FROM items ORDER BY score DESC;
+----+-------------+----------------+--------------------+
| id | description | tags           | score              |
+----+-------------+----------------+--------------------+
|  1 | the first   | tag1 tag3 tag4 | 0.6700310707092285 |
|  5 | the fifth   | tag4 tag3 tag6 | 0.6700310707092285 |
|  2 | the second  | tag5 tag1 tag2 |                  0 |
|  3 | the third   | tag5 tag1 tag9 |                  0 |
|  4 | the fourth  | tag5 tag6 tag2 |                  0 |
|  6 | the sixth   | tag2 tag3 tag6 |                  0 |
+----+-------------+----------------+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT *,MATCH(tags) AGAINST ('tag3 tag6 tag4' IN BOOLEAN MODE) as score FROM items ORDER BY score DESC;
+----+-------------+----------------+-------+
| id | description | tags           | score |
+----+-------------+----------------+-------+
|  5 | the fifth   | tag4 tag3 tag6 |     3 |
|  1 | the first   | tag1 tag3 tag4 |     2 |
|  6 | the sixth   | tag2 tag3 tag6 |     2 |
|  4 | the fourth  | tag5 tag6 tag2 |     1 |
|  2 | the second  | tag5 tag1 tag2 |     0 |
|  3 | the third   | tag5 tag1 tag9 |     0 |
+----+-------------+----------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT *,MATCH(tags) AGAINST ('+tag3 +tag6 +tag4' IN BOOLEAN MODE) as score FROM items ORDER BY score DESC;
+----+-------------+----------------+-------+
| id | description | tags           | score |
+----+-------------+----------------+-------+
|  5 | the fifth   | tag4 tag3 tag6 |     1 |
|  1 | the first   | tag1 tag3 tag4 |     0 |
|  2 | the second  | tag5 tag1 tag2 |     0 |
|  3 | the third   | tag5 tag1 tag9 |     0 |
|  4 | the fourth  | tag5 tag6 tag2 |     0 |
|  6 | the sixth   | tag2 tag3 tag6 |     0 |
+----+-------------+----------------+-------+
6 rows in set (0.00 sec)

mysql>

The solution would seem to be evaluate a BOOLEAN MODE score, and then a non-BOOLEAN MODE score as follows:

SELECT *,
MATCH(tags) AGAINST ('tag3 tag6 tag4') as score1,
MATCH(tags) AGAINST ('+tag3 +tag6 +tag4' IN BOOLEAN MODE) as score2
FROM items ORDER BY score2 DESC, score1 DESC;

Here is the result against your sample data:

mysql> SELECT *,
    -> MATCH(tags) AGAINST ('tag3 tag6 tag4') as score1,
    -> MATCH(tags) AGAINST ('+tag3 +tag6 +tag4' IN BOOLEAN MODE) as score2
    -> FROM items ORDER BY score2 DESC, score1 DESC;
+----+-------------+----------------+--------------------+--------+
| id | description | tags           | score1             | score2 |
+----+-------------+----------------+--------------------+--------+
|  5 | the fifth   | tag4 tag3 tag6 | 0.6700310707092285 |      1 |
|  1 | the first   | tag1 tag3 tag4 | 0.6700310707092285 |      0 |
|  2 | the second  | tag5 tag1 tag2 |                  0 |      0 |
|  3 | the third   | tag5 tag1 tag9 |                  0 |      0 |
|  4 | the fourth  | tag5 tag6 tag2 |                  0 |      0 |
|  6 | the sixth   | tag2 tag3 tag6 |                  0 |      0 |
+----+-------------+----------------+--------------------+--------+
6 rows in set (0.00 sec)

mysql>

or you could try not using the plus signs

mysql> SELECT *,
    -> MATCH(tags) AGAINST ('tag3 tag6 tag4') as score1,
    -> MATCH(tags) AGAINST ('tag3 tag6 tag4' IN BOOLEAN MODE) as score2
    -> FROM items ORDER BY score2 DESC, score1 DESC;
+----+-------------+----------------+--------------------+--------+
| id | description | tags           | score1             | score2 |
+----+-------------+----------------+--------------------+--------+
|  5 | the fifth   | tag4 tag3 tag6 | 0.6700310707092285 |      3 |
|  1 | the first   | tag1 tag3 tag4 | 0.6700310707092285 |      2 |
|  6 | the sixth   | tag2 tag3 tag6 |                  0 |      2 |
|  4 | the fourth  | tag5 tag6 tag2 |                  0 |      1 |
|  2 | the second  | tag5 tag1 tag2 |                  0 |      0 |
|  3 | the third   | tag5 tag1 tag9 |                  0 |      0 |
+----+-------------+----------------+--------------------+--------+
6 rows in set (0.00 sec)

mysql>

Either way, you will have to incorporate BOOLEAN MODE and non-BOOLEAN mode at the same time.

score 0 · Answer 3 · answered Sep 10 '12 at 22:09

0

Modify the order to order by score DESC, id DESC.
Assuming the score values are the same, the row with 5 will show up first.

answered Sep 10 '12 at 22:09

Kneel-Before-ZOD

4,141
1
24
26

that is right, what i can't understand is that 1 and 5 has the same score , cause 5 matchs all the keywords while 1 matchs only 2 of them :/ – itsme Sep 10 '12 at 22:14
By specifying 2 columns to order by, the data will be displayed in that order. So, using my code, **score** will be compared; if they are the same, then, **id** will be compared; if they are different, the higher digit will be displayed first. – Kneel-Before-ZOD Sep 10 '12 at 22:17
okok this is ok, but i can't understand why the same score :/ – itsme Sep 10 '12 at 22:18
why they has same score i mean 1 and 5 :/ – itsme Sep 10 '12 at 22:18
By default, MySQL orders rows of data using the column containing **auto_increment**, which is usually the column with the **primary key**. – Kneel-Before-ZOD Sep 10 '12 at 22:19
You'll have to check your data for why they are the same. Your code only selects; it didn't do any processing; so, those values came from your data. – Kneel-Before-ZOD Sep 10 '12 at 22:20
yes but score field is the same while 1 has no 'tag1' should be the word order? i mean 'tag2 tag3' is different from 'tag3 tag2' for mysql? do you know about? – itsme Sep 10 '12 at 22:21
why does id =5 has score = 0.67.. and not score =1 ? :D – itsme Sep 10 '12 at 22:22
sorry, I hate to downvote stuff, but this answer only applies to this particular search result set rather then solving the OQ's problem. – O. Jones Sep 10 '12 at 22:41
@OllieJones; that's not a problem. What's important is that the OP obtains the perfect answer. At least, that's what I would want. :-D – Kneel-Before-ZOD Sep 10 '12 at 22:52

MYsql FULLTEXT query yields unexpected ranking; why?

3 Answers3

Linked