MySQL full-text index ranking

Mysql> CREATE TABLE articles (ID INT UNSIGNED auto_increment not NULL PRIMARY KEY, title VARCHAR (200 ), Body TEXT, Fulltext (title,body), Engine=innodb; Query OK, 0 rows affected (2.48 sec) mysql> select * from articles; Empty Set (0.00 sec) mysql> INSERT into articles (Title,body) VALUES---' MySQL Tutorial ', ' This database Tutorial ("How to use MySQL", ' after you went through a ... '), (' Optimizing Your Database ', ' in this databas E tutorial ... '), (' MySQL vs. Yoursql ', ' When comparing databases ... '), (' MySQL Security ', ' when Configur Ed properly, MySQL ... '),--(' database, database, database ', ' Database Database '), (' 1001 MySQL Tr Icks ', ' 1. Never run mysqld as root. 2 ... '), (' MySQL full-text Indexes ', ' MySQL fulltext Indexes use a.. '); Query OK, 8 rows affected (0.06 sec) records:8 duplicates:0 warnings:0mysql> SELECT * from articles;+----+--------- ---------------------+-------------------------------------+| ID | Title |  Body |+----+------------------------------+-------------------------------------+| 1 | MySQL Tutorial |  This Database tutorial | | 2 | How to use MySQL |  After you went through a ... | | 3 | Optimizing Your Database |  In this database tutorial | | 4 | MySQL vs. Yoursql |  When comparing databases ... | | 5 | MySQL Security |  When configured properly, MySQL ... | | 6 | database, database, database |  Database Database | | 7 | 1001 MySQL Tricks | 1. Never run mysqld as root.  2 ..... | | 8 | MySQL Full-text Indexes |     MySQL fulltext indexes use a. |+----+------------------------------+-------------------------------------+8 rows in Set (0.00 sec) mysql> Select Id,title,body,match (Title,body) against ("Databas Fulltext") from articles WHEre match (Title,body) against ("Databas fulltext"), +----+-------------------------+------------------------------- --+-----------------------------------------------+| ID | Title | Body | Match (Title,body) against ("Databas fulltext") |+----+-------------------------+---------------------------------+  -----------------------------------------------+| 8 | MySQL Full-text Indexes | MySQL fulltext indexes use a.                            | 0.8155715465545654 |+----+-------------------------+---------------------------------+-------------------------- ---------------------+1 Row in Set (0.04 sec)

　　

Experiment:

mysql> select Id,title,body,match (title,body) against ("Database" in Boolean mode) as score from articles where match (title,body) against ("database Fulltext") Order BY score desc;+----+-------------- ----------------+---------------------------------+---------------------+| ID | Title | Body |  Score |+----+------------------------------+---------------------------------+---------------------+| 6 | database, database, database |  Database Database |  1.0886961221694946 | | 3 | Optimizing Your Database | In this database tutorial ... |  0.36289870738983154 | | 1 | MySQL Tutorial | This database tutorial ... |  0.18144935369491577 | | 8 | MySQL Full-text Indexes | MySQL fulltext indexes use a.                   | 0 |+----+------------------------------+---------------------------------+---------------------+4 rows in Set (0.00 SEC) 

Calculation method of Score:

Take the id=6 line for example:

The total number of records is: 8

The number of rows matched to database is: 3

The bank has a database number of 6

IDF = log10 (8/3)

TF = 6

Score= TF*IDF*IDF

Mysql> Select (log10 (8/3) *6*log10 (8/3)); +---------------------------+| (log10 (8/3) *6*log10 (8/3)) |+---------------------------+|         1.088696164686938 |+---------------------------+1 row in Set (0.00 sec)

　　

MySQL full-text index ranking