MySQL Performance: FullText => Logic behind full text search index

Members

Help

Home

Home » Performance » FullText » Logic behind full text search index

Show: Today's Messages :: Show Polls :: Message Navigator

Logic behind full text search index [message #1918]

Thu, 20 September 2007 12:14

srirams
Messages: 1
Registered: September 2007
Location: India

Junior Member

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

Report message to a moderator

Re: Logic behind full text search index [message #1931 is a reply to message #1918 ]

Mon, 24 September 2007 06:51

Speeple
Messages: 91
Registered: August 2006

Member

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.

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Previous Topic:	fulltext search with additional condition in the where clause
Next Topic:	AND as the default modifier for boolean search

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Tue Oct 7 13:31:55 EDT 2008

Total time taken to generate the page: 0.00827 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.