2

I got a table like this(I removed all those unrelated fields):

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL,
  `keywords` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `KEYWORDS_FTIDX` (`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And there are two rows:

  1. id: 131072, keywords: product1
  2. id: 131073, keywords: product2

If I run query SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE), it will return the first row as I expected.

But if I add Limit to the query SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 10, then I got nothing.

I try to use larger number for the LIMIT SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 100, it works again.

Could anyone please let me know how to make this right?

Sean
  • 63
  • 1
  • 7
  • Personally with all the added options you have going you should post this to Database Administrators community. That is if you understand all those extras you added to your T-SQL statements. If you don't simply it back some. – Edward Dec 27 '16 at 03:08
  • I just run mysql in a docker container with all default configurations for development purpose. So I don't think it's about the options. But thanks for your advise, I'll try Database Administrators community to see if there is a better solution. – Sean Dec 27 '16 at 08:23

1 Answers1

1

I read something about the full text index implementation long time ago. It's about some of the index engine just mark any removed records as deleted rather than remove them from the index, and those removed items can be hit by the index search operation and removed from the final results before it returns to the user.

So if mysql works this way, then I use LIMIT with MATCH, mysql may just search the very beginning of the index for better performance. But all those rows are already removed, so that's why I got empty result.

Then I did those and the result proved it:

  1. Create a test table use the exact same query I posted in the question part.
  2. Insert two rows: id: 1, keywords: product1, id: 2, keywords: product2.
  3. Run search query: SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 1, then I got the right result.
  4. Delete all those two records and insert them again.
  5. Run the query from Step 3, then I got nothing.
  6. But if I run: SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 2, it will return the right result again.

So for now, a quick fix can be combine the match condition with another one. In that case, mysql will fetch more than just needed in the match phase. And this query works: SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) AND id >= 0 LIMIT 1

I also ran explain on the query, it used the KEYWORDS_FTIDX index as well.

Those lines from MySQL Doc(https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html) may also about the problem:

Deleting a record that has a full-text index column could result in numerous small deletions in the auxiliary index tables, making concurrent access to these tables a point of contention. To avoid this problem, the Document ID (DOC_ID) of a deleted document is logged in a special FTS__DELETED table whenever a record is deleted from an indexed table, and the indexed record remains in the full-text index. Before returning query results, information in the FTS__DELETED table is used to filter out deleted Document IDs. The benefit of this design is that deletions are fast and inexpensive. The drawback is that the size of the index is not immediately reduced after deleting records. To remove full-text index entries for deleted records, you must run OPTIMIZE TABLE on the indexed table with innodb_optimize_fulltext_only=ON to rebuild the full-text index. For more information, see Optimizing InnoDB Full-Text Indexes.

Sean
  • 63
  • 1
  • 7