0

I have have a large MySQL database. One table 'news' has over 6million entries. Two columns are Arabic language text. I am able to create an index for the two columns using:

mysql> CREATE FULLTEXT INDEX news_index ON news(news_title, news_text);

but the index is empty and I receive the following error when I try to execute a fulltext search:

mysql> SELECT news_title FROM news WHERE MATCH(news_title) AGAINST('أردوغان');

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

The database used InnoDB and UTF-8. The column encoding was utf8_unicode_ci, I thought that may be the problem so I changed the two columns to utf8_general_ci.

When I created the index, the index list shows:

+-------+------------+---------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| news  |          0 | PRIMARY       |            1 | news_id      | A         |     4293286 |     NULL | NULL   |      | BTREE      |         |               |
| news  |          1 | fk_news_1_idx |            1 | news_country | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| news  |          1 | news_index    |            1 | news_title   | NULL      |     4293286 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| news  |          1 | news_index    |            2 | news_text    | NULL      |     4293286 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+---------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

I have also tried making an index with just:

mysql> ALTER TABLE news ADD FULLTEXT(news_title, news_text);

But again, no luck. I am missing something, when I create the index, it runs for over an hour so something is happening. What am I missing?

Boerjie
  • 43
  • 6

1 Answers1

1

As your fulltext index is applied on both news_title,news_text columns, you should use both of the to in MATCH() keyword

SELECT news_title FROM news WHERE MATCH(news_title,news_text) AGAINST('أردوغان');
Muhamed Alshafey
  • 41
  • 3
  • So simple! That worked, I didn't realize I had to run against both columns if the index was created for both. – Boerjie Apr 01 '19 at 14:14