‘CREATE INDEX’ for mysql Table if field is BLOBBy admin, written on February 28, 2010
Mysql allows the fulltext search. To do that, there must be an index
For example, you have two fields which you want to be searched:
my_correct_column of TEXT type
my_column of BLOB type
ALTER TABLE my_table ADD FULLTEXT `related_index` ( `my_correct_column` , `my_column` )
and get something like this:
ERROR 1170 (42000): BLOB/TEXT column ‘my_column’ used in key specification without a key length
ERROR 1283 (HY000): Column ‘my_column’ cannot be part of FULLTEXT index
👉 Problem is, that many text fields are stored in mysql not in TEXT format, but as BLOB (my_column).
MYSQL doesn’t allow indexing BLOB’s. The reason might be simple, that binary format should not be searched.
To add the index, format of field should be changed:
ALTER TABLE my_table MODIFY my_column TEXT;
💡 Remark: It also fixes the issue with many WordPress plugins, particularly of “what is related” type, complaining that they fail to create an index.
I had this problem with “Yet Another Related Posts Plugin (YARPP)”
INCOMING SEARCH TERMS