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?