Home » 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?
|
|
|
| Re: Search Not Using Index Across Join [message #3598 is a reply to message #3472 ] |
Wed, 01 October 2008 08:44  |
artur8ur
Messages: 23
Registered: September 2008 |
Junior Member |
|
|
| threadhead wrote on Wed, 27 August 2008 20:35 |
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 |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------------+------+-------------+
|
The Index is not used, because you mix two tables in one match...
MySQL cant use an index in this case...
| threadhead wrote on Wed, 27 August 2008 20:35 |
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)
)
|
|
|
| Re: Search Not Using Index Across Join [message #3600 is a reply to message #3598 ] |
Wed, 01 October 2008 20:55 |
threadhead
Messages: 2
Registered: August 2008
Location: AZ |
Junior Member |
|
|
Ah, I see now. You need to break it into two selects with a UNION DISTINCT to join the sets back together.
Thanks.
|
|
|
Goto Forum:
Current Time: Wed Jan 7 20:35:30 EST 2009
Total time taken to generate the page: 0.01342 seconds |
Members
Search
Help
Register
Login
Home
