‘CREATE INDEX’ for mysql Table if field is BLOB

By admin, written on February 28, 2010


  • Join date: 11-30-99
  • Posts: 52
View Counter:
Rate it
  • Which SQL implementations are mostly used in your projects?

    View Results

    Loading ... Loading ...
  • bodytext bodytext bodytext

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

You try:

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
or maybe
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)”

Be Sociable, Share!
Does that help to solve your problem?
VN:F [1.8.5_1061]
Rating: 0 (from 2 votes)
1 votes 'YES'  1 votes 'NO'


Be Sociable, Share!


Leave a Reply