How do you index a text column in MySQL?

Question

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'

Answer 1

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

• Rick James: bar(500) is also called a "prefix". I find that INDEX is almost never useful, whether applied to TEXT or some other datatype.
• Evan Carroll: INDEX is almost never useful -- what do you mean?
• Rick James: For most 'range' queries, the index is totally ignored. You can't say UNIQUE without understanding that the constraint applies only to the prefix. 'Point' queries might work, but that is only a subset of possible queries. I forget what other cases there might be. (Did you want to play tag on this question, too? Hehe.)
• Martin Zvarík: what is FOO and what is BAR ? tablename or colname or indexname?