Hello Daniel,
one remark:
a semantic of a left join is:
- take
ALL records from the left table, even there is no matching records in the right table
Since we must take
ALL records from the left table (in your case - wp_users), there is no sense to use any index on the left table,
the fastest and the simplest way is just to do a full table scan.
Looking at the query it seems obvious that MySql should use the index to match records in the right table.
However, your tables use different encodings, and this prevents mysql from using the index.
Run these commands:
EXPLAIN EXTENDED your_query;
SHOW warnings;
and you will see an actual query executed by mysql:
'Note', '1003', '/* select#1 */ select `test`.`e`.`firstname` AS `firstname`,`test`.`u`.`user_registered` AS `user_registered`
from `test`.`wp_users` `u` join `test`.`employee` `e`
where (`test`.`u`.`user_login` = convert(`test`.`e`.`username` using utf8))'
notice a call to convert() .... mysql must convert first the username column from latin1 to utf8, because it cannot compare values in different encodings.
And a call to this function prevents mysql from using any index.
Edited 1 time(s). Last edit at 03/05/2012 05:06PM by irek kordirko.