Menu
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?
739
Syntax. CREATE INDEX [index name] ON [Table name]([column1, column2, column3,...]); Multicolumn indexes can: be created on up to 32 columns.
An index can be defined on more than one column of a table. For example, if you have a table of this form: CREATE TABLE test2 ( major int, minor int, name varchar );
The columns in the fulltext index can be placed in any sort of order, but all the columns as specified when creating the fulltext index must be referenced in the match() query.
It is possible for an index to have two or more columns. Multi column indexes are also known as compound or concatenated indexes. Let us look at a query that could use two different indexes on the table based on the WHERE clause restrictions. We first create these indexes.
Fulltext with 2 columns you create like this
ALTER TABLE tbl_query ADD FULLTEXT INDEX `FullText` (`query_desc` ASC, `query_desc_details` ASC); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
