ALTER TABLE syntax

Once and a while I'll need to INDEX a field (or fields) in one of my MySQL tables that are of the BLOB/TEXT (mediumtext) type. To do this, you need to use the ALTER table query with a length specified. I usually forget the exact syntax to do this so I decided to post it here:

ALTER TABLE `table_name` ADD INDEX (`text_field`(1000));

If you're like me and you forget to add the character length, you'll probably receive a #1170 SQL-query error that reads something like:

#1170 - BLOB/TEXT column 'text_field' used in key specification without a key length

which might occur if you try to alter the table in the future using phpMyAdmin.

After running the alter table command above do a quick repair operation of the table to rebuild the indexes:

mysql> REPAIR TABLE tbl_name QUICK;