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
Made Friday, 23 of November , 2007 at 7:23 pm
[...] ways. Bad puns make good transitions, and Corrado Pandiani sheds light on MySQL’s rules for Charsets and Collations on Multicolumn Fulltext Index. Adam Douglas of Binary Expressions fixed some trouble with MySQL and French Characters not [...]

