Search Not Using Index Across Join

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 eventsLEFT 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 eventsLEFT 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?

[B]threadhead wrote on Wed, 27 August 2008 20:35[/B]

EXPLAIN SELECT * FROM eventsLEFT 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 | ±—±------------±--------------±-------±--------------±--------±--------±--------------------------------------------±-----±------------+

The Index is not used, because you mix two tables in one match…
MySQL cant use an index in this case…

[B]threadhead wrote on Wed, 27 August 2008 20:35[/B]

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?

In this second case, MySQL cant use the index because of the “or” in the statement.

How about:

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))UNION DISTINCT( SELECT * FROM events LEFT OUTER JOIN organizations ON organizations.id = events.organization_id WHERE MATCH( organizations.church_name, organizations.city, organizations.contact_name, organizations.email) AGAINST( ‘southern’ IN BOOLEAN MODE))

Ah, I see now. You need to break it into two selects with a UNION DISTINCT to join the sets back together.

Thanks.