Column Indexes index file much smaller. When you index a  BLOB  or  TEXT  column, you  must  specify a prefix length for the index. For example: CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); Prefixes can be up to 767 bytes long for  InnoDB  tables that use the  REDUNDANT  or  COMPACT  row format. The prefix length limit is 3072 bytes for  InnoDB  tables that use the  DYNAMIC  or  COMPRESSED  row format. For MyISAM tables, the prefix length limit is 1000 bytes. Prefix limits are measured in bytes, whereas the prefix length in  CREATE TABLE , ALTER TABLE , and  CREATE INDEX  statements is interpreted as number of characters for nonbinary string types ( CHAR ,  VARCHAR ,  TEXT ) and number of bytes for binary string types ( BINARY ,  VARBINARY ,  BLOB ). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set. If a search term exceeds the index prefix length, the index is used to exclude non-matching rows, and the remaining rows are examined for possible matches. For additional information about index prefixes, see Section 13.1.15, “CREATE INDEX Statement” . FULLTEXT  indexes are used for full-text searches. Only the  InnoDB  and  MyISAM  storage engines support FULLTEXT  indexes and only for  CHAR ,  VARCHAR , and  TEXT  columns. Indexing always takes place over the entire column and column prefix indexing is not supported. For details, see Section 12.10, “Full-Text Search Functions” . Optimizations are applied to certain kinds of  FULLTEXT  queries against single  InnoDB  tables. Queries with these characteristics are particularly efficient: For queries that contain full-text expressions, MySQL evaluates those expressions during the optimization phase of query execution. The optimizer does not just look at full-text expressions and make estimates, it actually evaluates them in the process of developing an execution plan. An implication of this behavior is that  EXPLAIN  for full-text queries is typically slower than for non-full-text queries for which no expression evaluation occurs during the optimization phase. EXPLAIN  for full-text queries may show  Select tables optimized away  in the  Extra  column due to matching occurring during optimization; in this case, no table access need occur during later execution. You can create indexes on spatial data types.  MyISAM  and  InnoDB  support R-tree indexes on spatial types. Other storage engines use B-trees for indexing spatial types (except for  ARCHIVE , which does not support spatial type indexing). Spatial Indexes FULLTEXT Indexes Note • FULLTEXT  queries that only return the document ID, or the document ID and the search rank. • FULLTEXT  queries that sort the matching rows in descending order of score and apply a  LIMIT  clause to take the top N matching rows. For this optimization to apply, there must be no  WHERE  clauses and only a single  ORDER BY  clause in descending order. • FULLTEXT  queries that retrieve only the  COUNT(*)  value of rows matching a search term, with no additional  WHERE  clauses. Code the  WHERE  clause as  WHERE MATCH( text ) AGAINST (’ other_text ’) , without any  > 0  comparison operator. 1718