Search Not Using Index Across Join

[threadhead]

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.

 

mysql  Ver 14.12 Distrib 5.0.45, for apple-darwin8.6.0 (powerpc) using readline 5.0

 

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?

 

And how do I structure the query to use both indices (events table and organizations table)?

 

Thanks!

[fenway]

Well, a FULLTEXT index (or any index, for that matter) cannot cross tables, so that's why you need to use separate MATCH...AGAINST clauses.

 

But why not just UNION the results of two separate queries?

[threadhead]

Well, a FULLTEXT index (or any index, for that matter) cannot cross tables, so that's why you need to use separate MATCH...AGAINST clauses.

But when I tried the separate MATCH...AGAINST clauses, it still didn't use either index. See query #3.

 

Only when I limited the MATCH fields to the SELECT table, did it use the index on the 'events' table. Unless I'm reading the EXPLAIN wrong (I certainly could), when a table has a 'type' of 'ALL' then it had to scan rows of the table and not utilize the index.

 

But why not just UNION the results of two separate queries?

 

I'm not familiar with the UNION. I tried a couple of queries and I'm just not getting the syntax correct. Do you have an example of using UNION with a JOIN?

[fenway]

Forget the JOIN for a minute... just UNION each table with it's own FULLTEXT index.

[threadhead]

OK, so I tried this:

 (SELECT * FROM  `events` WHERE
MATCH (events.theme, events.contact_name, events.contact_email, events.contact_phone)
AGAINST ('southern' IN BOOLEAN MODE))

UNION

(SELECT * FROM  `organizations` WHERE 
MATCH (organizations.church_name, organizations.city, organizations.contact_name, organizations.email)
AGAINST ('southern' IN BOOLEAN MODE))

 

and I get the error:

 

#1222 - The used SELECT statements have a different number of columns

 

So I go back and read the MySQL 5.0 Ref again, looks like the columns need to match in type and size. So:

 

(SELECT  `organization_id` FROM  `events` WHERE
MATCH (events.theme, events.contact_name, events.contact_email, events.contact_phone)
AGAINST ('southern' IN BOOLEAN MODE))

UNION

(SELECT  `id` FROM  `organizations` WHERE 
MATCH (organizations.church_name, organizations.city, organizations.contact_name, organizations.email)
AGAINST ('southern' IN BOOLEAN MODE))

 

And it give me the 28 rows I expect. But I need to get all the 'events' fields joined with their respective 'organizations'.

 

This is where I'm confused.

[fenway]

That's great -- the EXPLAIN should indicate that FULLTEXT is being used for both ... and I would all UNION ALL to save a filesort.

 

All of you need to do know is join the events to able to this union'ed table:

 

SELECT e.* FROM events 
INNER JOIN 
((SELECT  `organization_id` FROM  `events` WHERE
MATCH (events.theme, events.contact_name, events.contact_email, events.contact_phone)
AGAINST ('southern' IN BOOLEAN MODE))

UNION ALL

(SELECT  `id` AS organization_id FROM  `organizations` WHERE 
MATCH (organizations.church_name, organizations.city, organizations.contact_name, organizations.email)
AGAINST ('southern' IN BOOLEAN MODE)) ) AS both ON ( both.organization_id = e.organization_id )

[threadhead]

First, let me say thanks for the help. I've searched quite a bit over the past two days, and this scenario (which I think would be common) is just not discussed much. Or more likely, few every check the EXPLAIN to see that their indexed are not being used.

 

Your above query nets a:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'both ON ( both.organization_id = e.organization_id )' at line 11

 

I tried a few variations... keep getting the same error.

[fenway]

Silly me... both is a reserved keyword... I had no idea.

 

SELECT e.* FROM events AS e
INNER JOIN (

SELECT  `organization_id` FROM  `events` WHERE
MATCH (events.theme, events.contact_name, events.contact_email, events.contact_phone)
AGAINST ('southern' IN BOOLEAN MODE)

UNION ALL

SELECT  `id` AS organization_id FROM  `organizations` WHERE 
MATCH (organizations.church_name, organizations.city, organizations.contact_name, organizations.email)
AGAINST ('southern' IN BOOLEAN MODE)

) AS sub ON ( sub.organization_id = e.organization_id )

My bad.