Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Logic behind full text search index

sriramssrirams EntrantCurrent User Role Beginner
Hi,

I am trying to figure out the way fulltext search index works.

I have a table which have 5 columns.
3 columns have been added into full text search index.

Here is the same data of 2 rows and the data belonging to the full text index columns:

1) Row 1

+
| mytable_caption
+
| MySQL is the greatest database server int he world!
+

| mytable_full_body
---+| The MySQL database server is the world's most popular open source database. Over six million installations use MySQL to power high-volume Web sites and other critical business systems - including industry-leaders like The Associated Press,Yahoo, NASA, Sabre Holdings and Suzuki. |

+
+
| mytable_title |
+
+
| MySQL Database Server |
+
+

Row 2

+
mytable_caption
| PHP is a widely-used general-purpose scripting language that is especially sui
ted for Web development and can be embedded into HTML. |


+
| mytable_full_body
|
+


+
| PHP is the greatest web developing language in the world. PHP can perform func
tions that PERL performs on the web. |
+


+
+
| mytable_title |
+
+
| PHP Hypertext Processer |
+
+

That ends the data in 2 rows for 3 columns.

Now I am using the below query to do a full text search.



SELECT mytable_id, mytable_title, mytable_caption, mytable_dts, MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') AS score FROM mytable WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) AGAINST ('$searchstring') ORDER BY score DESC"</pre>


If $searchstring is mysql then my query returns with a score of
2.207764127185

But if $searchstring is php then the score is 0.

I don't see any big difference between the 2 rows. One is about php and the other is about mysql.
I was expecting the same score for both.

Could someone throw light on this?

Thanks
Sriram

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.