Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I followed this example from the Mysql Documentation:

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     );

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

SELECT *, MATCH ( title, body ) AGAINST ( 'database' ) AS Score
    FROM articles
    WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------+------------+
| id | title             | body                         | Score      |
+----+-------------------+------------------------------+------------+
|  5 | MySQL vs. YourSQL | In the following database... | 0.662664...|
|  1 | MySQL Tutorial    | DBMS stands for DataBase ... | 0.655458...|
+----+-------------------+------------------------------+------------+

this works, but if i delete all rows, but one:

DELETE FROM `articles` WHERE id >1

then the select gives Score 0:

SELECT * , MATCH ( title, body ) AGAINST ( 'database' ) AS Score FROM articles
share|improve this question

1 Answer 1

up vote 2 down vote accepted

With only one line left all occurances are 100% of the search, and everything above 50% is considered a stop word. My guess is that a stop word gets scored 0. There's also a stop word list (the word 'database' is not in it though) found here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

From http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html you find:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.

share|improve this answer
    
So how would you search then for rows with the word "database" if more than 50% contain the word? –  rubo77 Feb 15 '13 at 0:01
1  
SELECT * , MATCH ( title, body ) AGAINST ( 'database' IN BOOLEAN MODE) AS Score FROM articles; dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html "Boolean full-text searches have these characteristics: - They do not use the 50% threshold that applies to MyISAM search indexes ..." –  Bing Feb 17 '13 at 15:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.