How to create FULLTEXT index on multiple columns?

Question

I am running the following query on tbl_query

select * from tbl_query q where match(q.query_desc,q.query_desc_details) against ('test1' WITH QUERY EXPANSION);

It's giving an error

16:46:22    select * from tbl_query q where match(q.query_desc,q.query_desc_details) against ('test1' WITH QUERY EXPANSION) LIMIT 0, 1000   Error Code: 1191. Can't find FULLTEXT index matching the column list    0.078 sec

My table is like this

 CREATE TABLE `tbl_query` (
  `query_id` int(11) NOT NULL AUTO_INCREMENT,
  `query_desc` text NOT NULL,
  `query_desc_details` text,
  PRIMARY KEY (`query_id`),
  KEY `QUERY_DESC` (`query_desc`(333)) USING BTREE,
  KEY `QUERY_DESC_DETAILS` (`query_desc_details`(333)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

In database full text words boundaries are like

ft_max_word_len=    84
ft_min_word_len=    4

I am searching against two column.
So my question is how to create the full text index for the table?

Mad Dog Tannen · Accepted Answer · 2019-06-24 09:23:50Z

64

Fulltext with 2 columns you create like this

ALTER TABLE tbl_query
ADD FULLTEXT INDEX `FullText` 
(`query_desc` ASC, `query_desc_details` ASC);

edited Jun 24, 2019 at 9:23

answered Feb 4, 2014 at 11:57

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Supun Kavinda Over a year ago

What does ASC do?

roneo Over a year ago

@SupunKavinda 'ASC' indexes in ascending order whereas 'DESC' does it in descending

andrebruton Over a year ago

How do you use the 'FullText' index. I tried and it keeps saying Error 1054 Unknown column 'FullText'

Raghunandan Krishnamurthy · Accepted Answer · 2014-02-04 11:53:34Z

27

ALTER TABLE `TableName`
    ADD FULLTEXT INDEX `IndexName` (`ColumnName`);

answered Feb 4, 2014 at 11:53

Raghunandan Krishnamurthy

6955 silver badges11 bronze badges

Comments

A Paul · Accepted Answer · 2021-10-24 02:28:59Z

16

This creates the index. This should work.

ALTER TABLE `TableName` ADD FULLTEXT index_name(column1, column2);

edited Oct 24, 2021 at 2:28

answered Feb 4, 2014 at 11:47

A Paul

8,3033 gold badges33 silver badges65 bronze badges

4 Comments

Pradeep Kr Kaushal Over a year ago

I added the index but it's showing the same error. Something else need to be done?

A Paul Over a year ago

nope it should work. Check once useing desc if it is there. Try restarting the mysql once. But this should work.

Mad Dog Tannen Over a year ago

ALTER TABLE tbl_query ADD FULLTEXT index_name(query_desc,query_desc_details); for 2 columns.

VMN Over a year ago

@KayNelson Does not seem to work when the table has some content already and but when you create a fresh table it works. But is there as way to make it work on tables that is already has quite a bit of data ? I am using 10.1.21-MariaDB

Abdul Rehman · Accepted Answer · 2021-04-27 12:10:39Z

2

Add the Index

ALTER TABLE table_name ADD FULLTEXT index_name(column1, column2);

To get the search result

SELECT * FROM table_name WHERE MATCH(column1, column2)
AGAINST('search string' IN NATURAL LANGUAGE MODE);

To drop the index

ALTER TABLE table_name
DROP INDEX index_name;

edited Apr 27, 2021 at 12:10

answered Apr 27, 2021 at 12:05

Abdul Rehman

214 bronze badges

Comments

Jeremy Jones · Accepted Answer · 2024-11-14 16:57:12Z

0

These are all ALTER but you can also use the CREATE INDEX syntax to add it:

CREATE FULLTEXT INDEX search_idx ON myDatabase.books (bookTitle, bookAuthor, bookText);

answered Nov 14, 2024 at 16:57

Jeremy Jones

5,7614 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to create FULLTEXT index on multiple columns?

5 Answers 5

3 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related