charsets and collations on multicolumn fulltext index

Writing by corra on Tuesday, 20 of November , 2007 at 12:12 am

Today I answered to a problem regarding fulltext indexes on an italian newsgroup. The guy was in trouble in building a multicolumn fulltext index. MySQL always said that a column cannot be part of the index. Why?

Remember that all the columns in a fulltext index must have the same charset and the same collation.
Let’s try if it is true!

Create a sample news table and try to build the fulltext index on (news_title, news_text):

mysql> CREATE TABLE news(
        -> id INT auto_increment,
        -> news_title VARCHAR(100),
        -> news_text TEXT,
        -> PRIMARY KEY(id))
        -> CHARSET latin1 COLLATE latin1_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX ft_idx ON news(news_title,news_text);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Great, it works!

And now, change the charset of one of the field and try again:

mysql> ALTER TABLE news DROP INDEX ft_idx;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE news MODIFY COLUMN news_title VARCHAR(100) CHARSET utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE FULLTEXT INDEX ft_idx ON news(news_title,news_text);
ERROR 1283 (HY000): Column 'news_text' cannot be part of FULLTEXT index

And now, try to change the collation of one of fields:
(but first change the charset to the previous one)

mysql> ALTER TABLE news MODIFY COLUMN news_title VARCHAR(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE news MODIFY COLUMN news_text TEXT COLLATE latin1_swedish_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE FULLTEXT INDEX ft_idx ON news(news_title,news_text);
ERROR 1283 (HY000): Column 'news_text' cannot be part of FULLTEXT index

Same charsets and collations on a multicolumn fulltext index. That’s all.

Category: MySQLen

Name (required)

Mail (will not be published) (required)

Website

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>