Search code examples
sqlindexingtextsyntax

Is it possible to use Text for SQL Indexing

I got the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'summary' used in key specification without a key length in c:\website\someclass.php

Is it possible to use TEXT for SQL Indexing?

Here's my code:

CREATE TABLE conversation (
  conversation_id INT(7) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  some_id INT(7) UNSIGNED,
  summary TEXT,
  INDEX(conversation_id, some_id, summary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Solution

  • No, it is not possible to use the TEXT data type for indexing, as the error message suggests.

    The reason for this is that the TEXT data type can potentially store a large amount of data, and therefore cannot be indexed efficiently. The error message specifically refers to the fact that the summary column is being used in the index without specifying a length for the index key, which is not allowed for BLOB and TEXT columns.

    To fix this error, you can modify your conversation table to use a different data type for the summary column that can be indexed, such as VARCHAR or CHAR:

    CREATE TABLE conversation (
  conversation_id INT(7) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  some_id INT(7) UNSIGNED,
  summary VARCHAR(255),
  INDEX(conversation_id, some_id, summary(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;