Description:
The following query returns too few rows in MySQL 5.6 if table is in MyISAM:
mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC LIMIT 3;
+-------------------+--------------------+
| title | score |
+-------------------+--------------------+
| MySQL vs. YourSQL | 0.9562782645225525 |
| MySQL Tutorial | 0.5756555199623108 |
+-------------------+--------------------+
2 rows in set (0.00 sec)
In 5.5 we get the following result:
mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC LIMIT 3;
+-----------------------+--------------------+
| title | score |
+-----------------------+--------------------+
| MySQL vs. YourSQL | 0.9562782645225525 |
| MySQL Tutorial | 0.5756555199623108 |
| How To Use MySQL Well | 0 |
+-----------------------+--------------------+
3 rows in set (0.00 sec)
Note that without limit clause, also 5.6 will return rows with score == 0:
mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC;
+-----------------------+--------------------+
| title | score |
+-----------------------+--------------------+
| MySQL vs. YourSQL | 0.9562782645225525 |
| MySQL Tutorial | 0.5756555199623108 |
| How To Use MySQL Well | 0 |
| Optimizing MySQL | 0 |
| 1001 MySQL Tricks | 0 |
| MySQL Security | 0 |
+-----------------------+--------------------+
6 rows in set (0.00 sec)
How to repeat:
CREATE TABLE wp(
FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL,
dummy INTEGER,
PRIMARY KEY (FTS_DOC_ID),
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
FULLTEXT KEY idx (title,text)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO wp (title, text) VALUES
('MySQL Tutorial','DBMS stands for MySQL 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 to database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 3;
DROP TABLE wp;
Description: The following query returns too few rows in MySQL 5.6 if table is in MyISAM: mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC LIMIT 3; +-------------------+--------------------+ | title | score | +-------------------+--------------------+ | MySQL vs. YourSQL | 0.9562782645225525 | | MySQL Tutorial | 0.5756555199623108 | +-------------------+--------------------+ 2 rows in set (0.00 sec) In 5.5 we get the following result: mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC LIMIT 3; +-----------------------+--------------------+ | title | score | +-----------------------+--------------------+ | MySQL vs. YourSQL | 0.9562782645225525 | | MySQL Tutorial | 0.5756555199623108 | | How To Use MySQL Well | 0 | +-----------------------+--------------------+ 3 rows in set (0.00 sec) Note that without limit clause, also 5.6 will return rows with score == 0: mysql> SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC; +-----------------------+--------------------+ | title | score | +-----------------------+--------------------+ | MySQL vs. YourSQL | 0.9562782645225525 | | MySQL Tutorial | 0.5756555199623108 | | How To Use MySQL Well | 0 | | Optimizing MySQL | 0 | | 1001 MySQL Tricks | 0 | | MySQL Security | 0 | +-----------------------+--------------------+ 6 rows in set (0.00 sec) How to repeat: CREATE TABLE wp( FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL DEFAULT '', text MEDIUMTEXT NOT NULL, dummy INTEGER, PRIMARY KEY (FTS_DOC_ID), UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID), FULLTEXT KEY idx (title,text) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO wp (title, text) VALUES ('MySQL Tutorial','DBMS stands for MySQL 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 to database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); SELECT title, MATCH(title, text) AGAINST ('database') AS score FROM wp ORDER BY score DESC LIMIT 3; DROP TABLE wp;