Hi Leo,
I hadn't noticed this before so I had to dig a little to find out what was happening. EXPLAIN EXTENDED gave a hint:
EXPLAIN EXTENDED
(...)
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE customers ALL NULL NULL NULL NULL 1 100.00
1 SIMPLE AccountsReceivable ref customerId,datetype_idx customerId 195 func 1 100.00 Using index condition; Using where
Warnings:
Note 1003 select `test`.`AccountsReceivable`.`createUser` AS
`createUser`,`test`.`AccountsReceivable`.`created` AS
`created`,`test`.`customers`.`CustomerID` AS
`CustomerID`,`test`.`AccountsReceivable`.`type` AS
`type`,`test`.`AccountsReceivable`.`amount` AS
`amount`,`test`.`AccountsReceivable`.`invoiceNumber` AS
`invoiceNumber`,`test`.`customers`.`Phone` AS `Phone` from
`test`.`AccountsReceivable` join `test`.`customers` where
((`test`.`AccountsReceivable`.`createUser` <> '') and
(`test`.`AccountsReceivable`.`created` between '20071004' and '20071011') and
(`test`.`AccountsReceivable`.`type` = 'CHARGE') and
(`test`.`AccountsReceivable`.`customerId` =
convert(`test`.`customers`.`CustomerID` using utf8))) <<-- NOTICE THIS
If I change character set to UTF8 in both tables, I get this execution plan:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE customers ALL CustomerID_idx NULL NULL NULL 1 100.00 Using where
1 SIMPLE AccountsReceivable ref customerId,datetype_idx customerId 195 test.customers.CustomerID 1 100.00 Using where
So "func" means that an index lookup is done using customer.CustomerID values that go through conversion from latin1 to UTF8.
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com