12

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'

1 Answer 1

17

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

4
  • bar(500) is also called a "prefix". I find that INDEX is almost never useful, whether applied to TEXT or some other datatype.
    – Rick James
    Commented Jun 23, 2018 at 16:10
  • INDEX is almost never useful -- what do you mean? Commented Jun 23, 2018 at 16:56
  • 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.)
    – Rick James
    Commented Jun 23, 2018 at 18:44
  • 2
    what is FOO and what is BAR ? tablename or colname or indexname? Commented Apr 16, 2020 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.