I have a set of 4 tables that I want to search across. Each has a full text index. Can a query make use of every index?
CREATE TABLE `categories` (
`id` int(5) unsigned NOT NULL auto_increment,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `host_types` (
`id` int(5) unsigned NOT NULL auto_increment,
`category_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`category_id`,`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `hosts` (
`id` int(5) unsigned NOT NULL auto_increment,
`host_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`host_id`,`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL auto_increment,
`host_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`uid` varchar(10) default NULL,
`name` varchar(128) default NULL,
`keywords` text,
`description` text,
`price` decimal(10,2) default NULL,
`quantity` int(11) unsigned default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
FULLTEXT KEY `full_name` (`name`,`keywords`,`description`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
Here is my query;
SELECT categories.name AS category,
categories.id AS category_id,
host_types.name AS host_type,
host_types.id AS host_type_id,
hosts.name AS host,
hosts.id AS host_id,
products.name as name,
products.id AS product_id,
products.keywords as keywords,
products.description AS description,
products.quantity AS quantity,
products.price AS price,
products.uid as catalogue,
MATCH(categories.name, host_types.name, hosts.name, products.name,
products.keywords, products.description, products.uid)
AGAINST('search term') as score
FROM products
LEFT JOIN hosts ON products.host_id = hosts.id
LEFT JOIN host_types ON hosts.host_id = host_types.id
LEFT JOIN categories ON host_types.category_id = categories.id
WHERE MATCH(categories.name, host_types.name, hosts.name, products.name,
products.keywords, products.description, products.uid)
AGAINST('search term')
ORDER BY score DESC;
Here is my SQL structure, and I used the above Query.
SELECT
categories.name AS category,
categories.id AS category_id,
host_types.name AS host_type,
host_types.id AS host_type_id,
hosts.name AS host,
hosts.id AS host_id,
products.name as name,
products.id AS product_id,
products.keywords as keywords,
products.description AS description,
products.quantity AS quantity,
products.price AS price,
products.uid as catalgue
MATCH(categories.name) AGAINST('search term') as cscore,
MATCH(host_types.name) AGAINST('search term') as htscore,
MATCH(hosts.name) AGAINST('search term') as hscore,
MATCH(products.name, products.keywords, products.description, products.uid)
AGAINST('search term') as score
FROM products
LEFT JOIN hosts ON products.host_id = hosts.id
LEFT JOIN host_types ON hosts.host_id = host_types.id
LEFT JOIN categories ON host_types.category_id = categories.id
WHERE
MATCH(categories.name) AGAINST('search term') OR
MATCH(host_types.name) AGAINST('search term') OR
MATCH(hosts.name) AGAINST('search term') OR
MATCH(products.name, products.keywords, products.description, products.uid)
AGAINST('search term')
ORDER BY score DESC
CREATE TABLE `categories` (
`id` int(5) unsigned NOT NULL auto_increment,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `host_types` (
`id` int(5) unsigned NOT NULL auto_increment,
`category_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`category_id`,`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `hosts` (
`id` int(5) unsigned NOT NULL auto_increment,
`host_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`name` varchar(64) default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`host_id`,`display_order`),
FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL auto_increment,
`host_id` int(5) unsigned default NULL,
`display_order` int(5) unsigned default NULL,
`uid` varchar(10) default NULL,
`name` varchar(128) default NULL,
`keywords` text,
`description` text,
`price` decimal(10,2) default NULL,
`quantity` int(11) unsigned default NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
FULLTEXT KEY `full_name` (`name`,`keywords`,`description`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
You can't define fulltext indexes (or any kind of index) across multiple tables in MySQL. Each index definition references exactly one table. All columns in a given fulltext index must be from the same table.
The columns named as arguments to the MATCH()
function must be part of a single fulltext index. You can't use a single call to MATCH()
to search all columns that are part of all fulltext indexes in your database.
Fulltext indexes only index columns defined with CHAR
, VARCHAR
, and TEXT
datatypes.
You can define a fulltext index in each table.
Example:
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
FULLTEXT INDEX ftcat (name)
);
CREATE TABLE host_types (
id SERIAL PRIMARY KEY,
category_id BIGINT UNSIGNED,
name VARCHAR(100),
FULLTEXT INDEX ftht (name)
);
CREATE TABLE hosts (
id SERIAL PRIMARY KEY,
host_id BIGINT UNSIGNED,
category_id BIGINT UNSIGNED,
name VARCHAR(100),
FULLTEXT INDEX fthost (name)
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
keywords VARCHAR(100),
uid VARCHAR(100),
description VARCHAR(100),
quantity INTEGER,
price NUMERIC(9,2),
host_id BIGINT UNSIGNED,
FULLTEXT INDEX ftprod (name, keywords, description, uid)
);
And then you can write a query that uses each respective fulltext index:
SELECT ...
MATCH(categories.name) AGAINST('search term') as cscore,
MATCH(host_types.name) AGAINST('search term') as htscore,
MATCH(hosts.name) AGAINST('search term') as hscore,
MATCH(products.name, products.keywords, products.description, products.uid)
AGAINST('search term') as score
FROM products
LEFT JOIN hosts ON products.host_id = hosts.id
LEFT JOIN host_types ON hosts.host_id = host_types.id
LEFT JOIN categories ON host_types.category_id = categories.id
WHERE
MATCH(categories.name) AGAINST('search term') OR
MATCH(host_types.name) AGAINST('search term') OR
MATCH(hosts.name) AGAINST('search term') OR
MATCH(products.name, products.keywords, products.description, products.uid)
AGAINST('search term')
ORDER BY score DESC;
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