Forum Stats

  • 3,733,040 Users
  • 2,246,686 Discussions
  • 7,856,490 Comments

Discussions

Full text index in MySQL 5.5 MyISAM

3837224
3837224 Member Posts: 6
edited November 2018 in MySQL Community Space

Dear all,

A full text index is used while performing LIKE queries?

There are differences between this index operate in InooDB vs MyISAM engine?

Thanks in advance,

Bets regards,

Andrea

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited November 2018 Accepted Answer

    You should read the relevant version related documentation.

    For mysql 5.5:

    https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

    Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

    Notice Full-text indexes can be used with InnoDB tables "In MySQL 5.6 and up"... so 5.5 does NOT support Full-text indexes on InnoDB tables.

    Using the LIKE operator will NOT use a Full-text index. The syntax to use a Full-text index is to use "MATCH () ... AGAINST".

    Using the LIKE operator will use a normal index, if there is one defined on "col" AND the search string does NOT begin with a percent sign.

    https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

    B-Tree Index Characteristics    A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes: <span class="keyword token">SELECT</span> <span class="operator token">*</span> <span class="keyword token">FROM</span> <em class="replaceable">tbl_name</em> <span class="keyword token">WHERE</span> <em class="replaceable">key_col</em> <span class="operator token">LIKE</span> <span class="string token">'Patrick%'</span><span class="punctuation token">;</span><br/><span class="keyword token">SELECT</span> <span class="operator token">*</span> <span class="keyword token">FROM</span> <em class="replaceable">tbl_name</em> <span class="keyword token">WHERE</span> <em class="replaceable">key_col</em> <span class="operator token">LIKE</span> <span class="string token">'Pat%_ck%'</span><span class="punctuation token">;</span>

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited November 2018 Accepted Answer

    You should read the relevant version related documentation.

    For mysql 5.5:

    https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

    Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

    Notice Full-text indexes can be used with InnoDB tables "In MySQL 5.6 and up"... so 5.5 does NOT support Full-text indexes on InnoDB tables.

    Using the LIKE operator will NOT use a Full-text index. The syntax to use a Full-text index is to use "MATCH () ... AGAINST".

    Using the LIKE operator will use a normal index, if there is one defined on "col" AND the search string does NOT begin with a percent sign.

    https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

    B-Tree Index Characteristics    A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes: <span class="keyword token">SELECT</span> <span class="operator token">*</span> <span class="keyword token">FROM</span> <em class="replaceable">tbl_name</em> <span class="keyword token">WHERE</span> <em class="replaceable">key_col</em> <span class="operator token">LIKE</span> <span class="string token">'Patrick%'</span><span class="punctuation token">;</span><br/><span class="keyword token">SELECT</span> <span class="operator token">*</span> <span class="keyword token">FROM</span> <em class="replaceable">tbl_name</em> <span class="keyword token">WHERE</span> <em class="replaceable">key_col</em> <span class="operator token">LIKE</span> <span class="string token">'Pat%_ck%'</span><span class="punctuation token">;</span>
  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited November 2018

    I find full text in InnoDB easier to use especially with the BOOLEAN mode

    Plus the MeCab functionality is useful in many languages.

Sign In or Register to comment.