mysql - 为文本类型列创建索引

Question

对于超过10L行(每行500个字符)的表，如何在mysql中高效地为文本数据类型的列创建索引。

Answer 1

如果您正在使用 innodb，您应该创建一个 FULLTEXT 索引。

FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns, omitting any words that are defined as stopwords.

请参阅MySQL FULLTEXT documentation .

mysql> CREATE TABLE opening_lines (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   opening_line TEXT(500),
   author VARCHAR(200),
   title VARCHAR(200),
   FULLTEXT idx (opening_line)
) ENGINE=InnoDB;