error 1191 in database when doing a search

Question

I have been trying to resolve this issue for a while but I'm stuck.

I have a error keep throwing up every time I do a search on my website for "eastlincs" but I'm not sure how to fix the issue.

I have googled the error and there is not much about it and have read a couple of threads on here about the problem but I'm unable to fix the issue. I know its something to do with my database and adding the FULLTEXT to categories in my database.

The error being displayed:

A Database Error Occurred Error Number: 1191
Can't find FULLTEXT index matching the column list

This is the actual query:

SELECT 
    c.id, c.type_id, 
    MATCH(c.name) AGAINST('>eastlincs eastlincs*' IN BOOLEAN MODE) AS categories_name_score, 
    MATCH(c.meta_title) AGAINST('>eastlincs eastlincs*' IN BOOLEAN MODE) AS categories_meta_title_score, 
    MATCH(c.meta_description) AGAINST('>eastlincs eastlincs*' IN BOOLEAN MODE) AS categories_meta_description_score 
FROM (categories AS c) 
WHERE MATCH(c.name, c.meta_title, c.meta_description) AGAINST('>eastlincs eastlincs*' IN BOOLEAN MODE) 
ORDER BY (categories_name_score*1.25+categories_meta_title_score+categories_meta_description_score*1.20) DESC

UPDATE: table definition:

categories
CREATE TABLE `categories` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `type_id` int(11) NOT NULL DEFAULT '0',
 `parent_category_id` int(11) NOT NULL DEFAULT '0',
 `tree_path` varchar(255) NOT NULL,
 `order_num` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `seo_name` varchar(255) NOT NULL DEFAULT '',
 `selected_icons_serialized` text NOT NULL,
 `meta_title` varchar(255) NOT NULL,
 `meta_description` varchar(255) NOT NULL,
 `en_name` varchar(255) NOT NULL DEFAULT 'untranslated',
 `en_meta_title` varchar(255) NOT NULL DEFAULT 'untranslated',
 `en_meta_description` varchar(255) NOT NULL DEFAULT 'untranslated',
 PRIMARY KEY (`id`),
 KEY `parent_category_id` (`parent_category_id`),
 KEY `tree_path` (`tree_path`),
 FULLTEXT KEY `name` (`name`,`meta_title`,`meta_description`),
 FULLTEXT KEY `meta_title` (`meta_title`),
 FULLTEXT KEY `en_name` (`en_name`,`en_meta_title`,`en_meta_description`),
 FULLTEXT KEY `en_meta_title` (`en_meta_title`),
 FULLTEXT KEY `meta_description` (`meta_description`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8 PACK_KEYS=0

Answer 1

the error is quite self descriptive:

Can't find FULLTEXT index matching the column list

You are trying to use FULLTEXT search query against columns which may have not been added to the FULLTEXT index. Maybe your full text index doesn't exist at all.

So check the existence of your FULLTEXT index and if it exists, check the actual list of columns included in the index.

You can check the indexes defined for your table like this:

SHOW INDEX FROM categories;

More specifically check the list of columns in the FULLTEXT index if it exists:

SELECT column_name 
FROM INFORMATION_SCHEMA.STATISTICS
WHERE (table_schema, table_name) = ('mydatabase', 'categories')
  AND index_type = 'FULLTEXT'
ORDER BY seq_in_index;

(replace mydatabase with your actual db name)

and see if name, meta_title and meta_description are listed. If not, you need to add the missing columns to your index.

UPDATE: You do FULLTEXT query against name, meta_title and meta_description separately from each other and then against 3 columns at once: MATCH(c.name, c.meta_title, c.meta_description).

So I think you're missing separate FULLTEXT keys for name and meta_description:

ALTER TABLE categories ADD FULLTEXT INDEX meta_description (meta_description); 
ALTER TABLE categories ADD FULLTEXT INDEX name1 (name); 

HTH