2

Why do these two queries behave differently:

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------+----------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref   | new_hh_id     | new_hh_id | 9       | const |       15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | index | NULL          | BAARn     | 126     | NULL  | 83236197 | Using index |

mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on cast(FOOBAAR_hhs.relationship_group as char) = FOOBAAR.relationship_group where new_hh_id = 15387929;

| id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | FOOBAAR_hhs | ref  | new_hh_id     | new_hh_id | 9       | const |   15 | Using where |
|  1 | SIMPLE      | FOOBAAR     | ref  | BAARn         | BAARn     | 22      | func  |    3 | Using index |

?

The relationship_group members of both relations are typed VARCHAR(20) NOT NULL, but it is only when an explicit CAST is invoked that MySQL permits use of the available index?

(This is MySQL version 5.1.52-log)

  • Hi Martin, welcome to the site. Can you post the DDL (SHOW CREATE TABLE statements) for both tables? Also, what engine? – Derek Downey Apr 16 '12 at 17:37
1

You need to make absolutely sure the character sets of the tables are identical. Keep in mind that a unicode issues could be at play here.

You could redo the query like this:

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
on FOOBAAR.relationship_group = FOOBAAR_hhs.relationship_group where new_hh_id = 15387929;

or

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
USING (relationship_group) where new_hh_id = 15387929;

Try the explain for these and see if it changes

  • @Martin I presume this was directed at Rolando so I've converted it to a comment on the answer. Please consider 'accepting' the answer? – Jack says try topanswers.xyz May 7 '12 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.