MySQL Performance: FullText => Search Not Using Index Across Join

Search Not Using Index Across Join [message #3472]

Wed, 27 August 2008 14:35

threadhead
Messages: 2
Registered: August 2008
Location: AZ

Junior Member

When I perform a full text query across two tables, it doesn't appear that the fulltext indices are being used. Or maybe it is, and I just don't see it. Here are my two tables:

CREATE TABLE `events` (
  id int(11) NOT NULL auto_increment,
  organization_id int(11) default NULL,
  theme varchar(255) default NULL,
  contact_name varchar(255) default NULL,
  contact_phone varchar(255) default NULL,
  contact_email varchar(255) default NULL,
  website varchar(255) default NULL,
  created_at datetime default NULL,
  updated_at datetime default NULL,
  PRIMARY KEY  (id),
  KEY index_events_on_random_id (random_id),
  KEY index_events_on_organization_id (organization_id),
  FULLTEXT KEY text_search (theme,contact_name,contact_phone,contact_email)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


CREATE TABLE organizations (
  id int(11) NOT NULL auto_increment,
  church_name varchar(255) default NULL,
  address1 varchar(255) default NULL,
  address2 varchar(255) default NULL,
  city varchar(255) default NULL,
  state varchar(255) default NULL,
  zip_code varchar(255) default NULL,
  phone varchar(255) default NULL,
  email varchar(255) default NULL,
  website varchar(255) default NULL,
  contact_name varchar(255) default NULL,
  created_at datetime default NULL,
  updated_at datetime default NULL,
  PRIMARY KEY  (id),
  FULLTEXT KEY text_search (church_name,city,email,contact_name)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

When I run this query, the explain tells me it is not using the FULLTEXT index.

EXPLAIN SELECT * FROM `events`
LEFT OUTER JOIN `organizations` ON `organizations`.id = `events`.organization_id WHERE
(MATCH(  events.theme,  events.contact_name,  events.contact_email,  events.contact_phone,organizations.church_name,  organizations.city,  organizations.contact_name,  organizations.email) AGAINST('southern' IN BOOLEAN MODE));

+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                         | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+
|  1 | SIMPLE      | events        | ALL    | NULL          | NULL    | NULL    | NULL                                        | 1810 |             | 
|  1 | SIMPLE      | organizations | eq_ref | PRIMARY       | PRIMARY | 4       | vbsfinde_development.events.organization_id |    1 | Using where | 
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+

But if I simply remove the search on the 'organizations' fields, it does use the index.

EXPLAIN SELECT * FROM `events`
LEFT OUTER JOIN `organizations` ON `organizations`.id = `events`.organization_id WHERE
(MATCH(  events.theme,  events.contact_name,  events.contact_email,  events.contact_phone) AGAINST('southern' IN BOOLEAN MODE));

+----+-------------+---------------+----------+---------------+-------------+---------+---------------------------------------------+------+-------------+
| id | select_type | table         | type     | possible_keys | key         | key_len | ref                                         | rows | Extra       |
+----+-------------+---------------+----------+---------------+-------------+---------+---------------------------------------------+------+-------------+
|  1 | SIMPLE      | events        | fulltext | text_search   | text_search | 0       |                                             |    1 | Using where | 
|  1 | SIMPLE      | organizations | eq_ref   | PRIMARY       | PRIMARY     | 4       | vbsfinde_development.events.organization_id |    1 |             | 
+----+-------------+---------------+----------+---------------+-------------+---------+---------------------------------------------+------+-------------+

So, I tried this, and it still does not use the FULLTEXT index:

EXPLAIN SELECT * FROM `events` LEFT OUTER JOIN `organizations` ON `organizations`.id = `events`.organization_id WHERE    -> (MATCH(    ->  events.theme,
    events.contact_name,
    events.contact_email,
    events.contact_phone) AGAINST('southern' IN BOOLEAN MODE))
   OR
   (MATCH(
    organizations.church_name,
    organizations.city,
    organizations.contact_name,
    organizations.email) AGAINST('southern' IN BOOLEAN MODE));

+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                         | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+
|  1 | SIMPLE      | events        | ALL    | NULL          | NULL    | NULL    | NULL                                        | 1810 |             | 
|  1 | SIMPLE      | organizations | eq_ref | PRIMARY       | PRIMARY | 4       | vbsfinde_development.events.organization_id |    1 | Using where | 
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+

Why does it not use the FULLTEXT index on the 'organizations' table when I include the 'orgnizations' fields?

Report message to a moderator