is it possible to count how many words are matched for every row with MATCH AGAINST

Question

Is it possible directly in the mysql query? Example:

search in boolean mode +possible +know

"I want to know how this is possible" => 2 matches

"Everything is possible" => 1 match

Answer 1

UPDATE I moved my original answer down

I have a weird suggestion

You may need to use the mysql utility called myisam_ftdump

Here is a FULLTEXT dump from the sample in my original answer

C:\MySQL_5.5.12\data\sandro>myisam_ftdump -vc txtdata 1
        2            0.4054651 everyhing
        2            0.4054651 impossible
        1            1.3862944 knew
        3           -0.4054651 know
        2            0.4054651 nothing
        1            1.3862944 people
        2            0.4054651 possible
        1            1.3862944 probable
        1            1.3862944 something

If you can generate this as a text file, you could have PHP parse it for the word you are looking for.

ORIGINAL ANSWER

With or without BOOLEAN MODE, the answer is no.

However, you can show the ranking based on word occurrence and overall string length as follows:

SAMPLE DATA

DROP DATABASE sandro;
CREATE DATABASE sandro;
use sandro
CREATE TABLE txtdata
(
    id int not null auto_increment,
    txt VARCHAR(255),
    primary key (id),
    FULLTEXT (txt)
) ENGINE=MyISAM;
INSERT INTO txtdata (txt) VALUES
('I know Nothing is possible'),
('We know nothing is impossible'),
('I knew everyhing is possible'),
('We know everyhing is possible'),
('For may people something is probable');

Here is the result of various search rankings

mysql> SELECT *,MATCH(txt) AGAINST ('possible knew') as score FROM txtdata;
+----+--------------------------------------+--------------------+
| id | txt                                  | score              |
+----+--------------------------------------+--------------------+
|  1 | I know Nothing is possible           | 0.3919430673122406 |
|  2 | We know nothing is impossible        |                  0 |
|  3 | I knew everyhing is possible         |   1.73200523853302 |
|  4 | We know everyhing is impossible      |                  0 |
|  5 | For may people something is probable |                  0 |
+----+--------------------------------------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT *,MATCH(txt) AGAINST ('possible know') as score FROM txtdata;
+----+--------------------------------------+--------------------+
| id | txt                                  | score              |
+----+--------------------------------------+--------------------+
|  1 | I know Nothing is possible           | 0.3919430673122406 |
|  2 | We know nothing is impossible        |                  0 |
|  3 | I knew everyhing is possible         | 0.3919430673122406 |
|  4 | We know everyhing is impossible      |                  0 |
|  5 | For may people something is probable |                  0 |
+----+--------------------------------------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT *,MATCH(txt) AGAINST ('impossible knew') as score FROM txtdata;
+----+--------------------------------------+--------------------+
| id | txt                                  | score              |
+----+--------------------------------------+--------------------+
|  1 | I know Nothing is possible           |                  0 |
|  2 | We know nothing is impossible        | 0.3919430673122406 |
|  3 | I knew everyhing is possible         |  1.340062141418457 |
|  4 | We know everyhing is impossible      | 0.3919430673122406 |
|  5 | For may people something is probable |                  0 |
+----+--------------------------------------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT *,MATCH(txt) AGAINST ('impossible know') as score FROM txtdata;
+----+--------------------------------------+--------------------+
| id | txt                                  | score              |
+----+--------------------------------------+--------------------+
|  1 | I know Nothing is possible           |                  0 |
|  2 | We know nothing is impossible        | 0.3919430673122406 |
|  3 | I knew everyhing is possible         |                  0 |
|  4 | We know everyhing is impossible      | 0.3919430673122406 |
|  5 | For may people something is probable |                  0 |
+----+--------------------------------------+--------------------+
5 rows in set (0.00 sec)

mysql>