# Logic behind full text search index

**URL:** <https://forums.percona.com/t/logic-behind-full-text-search-index/2067>\
**Category:** Sphinx & Full-Text Search\
**Created:** [September 20, 2007, 10:44am UTC](https://forums.percona.com/t/logic-behind-full-text-search-index/2067 "2007-09-20T10:44:07Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![srirams](https://avatars.discourse-cdn.com/v4/letter/s/b4bc9f/32.png) [@srirams](https://forums.percona.com/u/srirams)\
**Post date:** [September 20, 2007, 10:44am UTC](https://forums.percona.com/t/logic-behind-full-text-search-index/2067/1 "2007-09-20T10:44:07Z")

</div>

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"

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

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [September 24, 2007, 5:21am UTC](https://forums.percona.com/t/logic-behind-full-text-search-index/2067/2 "2007-09-24T05:21:17Z")

</div>

Suggested reading:  
[http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning](http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning). html

The default minimum word length with MyISAM FT indexes is 4 bytes.

You can alter this in my.cnf by setting ft\_min\_word\_len e.g.

ft\_min\_word\_len=2

You have to restart MySQL server and then rebuild the fulltext indexes after making this change.