CODEWITHSUNDEEP
mysqldatabase
Pradeep Kr Kaushal
Pradeep Kr Kaushal

Reputation: 1546

How to create FULLTEXT index on multiple columns?

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?

Upvotes: 38

Views: 75015

Answers (5)

Jeremy Jones
Jeremy Jones

Reputation: 5641

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);

Upvotes: 0

A Paul
A Paul

Reputation: 8261

This creates the index. This should work.

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

Upvotes: 16

Abdul Rehman
Abdul Rehman

Reputation: 21

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;

Upvotes: 2

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Fulltext with 2 columns you create like this

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

Upvotes: 64

Raghunandan Krishnamurthy
Raghunandan Krishnamurthy

Reputation: 695

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

Upvotes: 27

Related Questions