Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Search Not Using Index Across Join

threadheadthreadhead EntrantInactive User Role Beginner
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;</pre>


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));</pre>



+----+
+
+
+
+
+
+
+
+
+| 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 | +----+
+
+
+
+
+
+
+
+
+</pre>

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));</pre>


+----+
+
+
+
+
+
+
+
+
+| 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 | | +----+
+
+
+
+
+
+
+
+
+</pre>

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));</pre>


+----+
+
+
+
+
+
+
+
+
+| 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 | +----+
+
+
+
+
+
+
+
+
+</pre>


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

Comments

  • artur8urartur8ur Contributor Current User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">threadhead wrote on Wed, 27 August 2008 20:35</td></tr><tr><td class="quote">


    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));</pre>



    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +| 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 | +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +</pre>


    </td></tr></table>

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

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">threadhead wrote on Wed, 27 August 2008 20:35</td></tr><tr><td class="quote">


    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));</pre>


    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +| 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 | +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +</pre>


    Why does it not use the FULLTEXT index on the 'organizations' table when I include the 'orgnizations' fields?
    </td></tr></table>

    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))</pre>
  • threadheadthreadhead Entrant Inactive User Role Beginner
    Ah, I see now. You need to break it into two selects with a UNION DISTINCT to join the sets back together.

    Thanks.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.