Databases
StackOverflow2013
Postsdbo
POmysql fulltext index: why result of match against is not as good as result of like?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql fulltext index: why result of match against is not as good as result of like?
    text
    copied!<ol> <li>I have created index and fulltext index of mysql, and the backend storage engine is MyISAM.<br> <code> mysql> show index from play_movie;<br> +------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |<br> +------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br> | play_movie | 0 | PRIMARY | 1 | id | A | 42782 | NULL | NULL | | BTREE | |<br> | play_movie | 0 | name | 1 | name | A | 42782 | NULL | NULL | | BTREE | |<br> | play_movie | 1 | play_movie_ec9d726c | 1 | describe | A | 1944 | 333 | NULL | | BTREE | |<br> | play_movie | 1 | name_2 | 1 | name | NULL | 42782 | NULL | NULL | | FULLTEXT | |<br> +------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br> </code> </li> <li> the result of match against.<br> <code> mysql> select name from play_movie where match(name) against ('约会规则'); +------------------------+<br> | name |<br> +------------------------+<br> | 约会规则 第二季 |<br> | 约会规则 第一季 |<br> +------------------------+<br> 2 rows in set (0.00 sec)<br> </code> </li> <li> the result of like.<br> <code> mysql> select name from play_movie where name like '%约会规则%';<br> +------------------------------------+<br> | name |<br> +------------------------------------+<br> | 恋爱法则/约会规则第四季 |<br> | 约会规则 第一季 |<br> | 约会规则 第二季 |<br> | 约会规则第三季 |<br> | 约会规则第六季 |<br> +------------------------------------+<br> 5 rows in set (0.04 sec)<br> </code> </li> <li> explain of the above 2 select.<br> <code> mysql> explain select name from play_movie where match(name) against ('约会规则');<br> +----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br> +----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br> | 1 | SIMPLE | play_movie | fulltext | name_2 | name_2 | 0 | | 1 | Using where |<br> +----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br> <br> mysql> explain select name from play_movie where name like '%约会规则%';<br> +----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br> +----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br> | 1 | SIMPLE | play_movie | index | NULL | name | 767 | NULL | 42782 | Using where; Using index |<br> +----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br> </code> </li> <li> like use 0.04 sec to find the result, match against use 0.00 sec to find the result, but like find better result than match against. </li> <li> the result of match against, the result looks like <strong>约会规则 第一季<strong>, the missed result looks like <strong>约会规则第六季</strong>, and the keyword is <strong>约会规则</strong>. it seems like fulltext index didn't split <strong>约会规则第六季</strong> into 约会规则</strong> and 第六季</strong>.<br> how can i config mysql or fulltext index to solve this problem? the above words are Chinese, and the default character set is utf8. </li> </ol>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload