Logic behind full text search index

Polyglot Projects Sphinx & Full-Text Search
1

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

2

Suggested reading:
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.