Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
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
add comment

1 Answer 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

share|improve this answer
    
Thanks; it was indeed an unnoticed character set issue –  Martin Keegan May 6 '12 at 3:35
    
@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 Douglas May 7 '12 at 10:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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