title: mySQL MATCH across multiple tables
body: <p>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?</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Here is my query;</p>
  
  <pre><code>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;
  </code></pre>
  
  <ul>
  <li>categories.name == FULLTEXT - 1</li>
  <li>host_types.name == FULLTEXT - 2</li>
  <li>hosts.name      == FULLTEXT - 3</li>
  <li>products.name, products.keywords, products.description, products.uid  == FULLTEXT - 4</li>
  </ul>
  
  <hr>
  
  <p>Here is my SQL structure, and I used the above Query.</p>
  
  <pre><code>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;
  </code></pre>