How do you index a text column in MySQL?

If I create a table with a text column

CREATE TABLE foo ( bar text );

I can't index the bar column.

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column 'bar' used in key specification without a key length

If I HELP CREATE INDEX, I get,

Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string';

KEY_LENGTH not mentioned only KEY_BLOCK_SIZE, but if I use it as an option, I get

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar ) KEY_LENGTH = 5;
ERROR 1911 (HY000): Unknown option 'KEY_LENGTH'

In MySQL Key Length is added as a type-modifier and placed in parenthesis colname(), you can provide it to CREATE INDEX like this,

CREATE INDEX foo_bar_idx ON foo ( bar(500) );

It's part of index_col_name, (except it's not optional on text so ignore the [])

index_col_name:
  col_name [(length)] [ASC | DESC]

Alternatively you may want an Full Text Search (FTS) index instead providing MATCH functionality

CREATE FULLTEXT INDEX foo_bar_ftsidx ON foo ( bar );

For more information see the MySQL docs on Full Text Search

StackExchange Database Administrators Q#210403, answer score: 16