I am trying a left outer join between two tables, and for some reason, the obvious index isn't being used and it's doing a table scan. Adding an index hint didn't help. Changing the join to an inner join makes the index get used. How can I use an index here? Any advice appreciated!
FYI, this is mysql Ver 14.14 Distrib 5.5.19, for Win64 (x86).
The first table is the wp_users table from WordPress, where I want to join by user_login with the index user_login_key:
CREATE TABLE `wp_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) NOT NULL DEFAULT '', /* Joining by this column */
`user_pass` varchar(64) NOT NULL DEFAULT '',
`user_nicename` varchar(50) NOT NULL DEFAULT '',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_url` varchar(100) NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(60) NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`), /* Here is the index */
KEY `user_nicename` (`user_nicename`)
) ENGINE=InnoDB AUTO_INCREMENT=729 DEFAULT CHARSET=utf8
The second table is my own, where the join will use the "username" column and the "employee_username_index" index:
CREATE TABLE `employee` (
`employee_id` int(10) unsigned NOT NULL auto_increment,
`alt_user_id` int(5) unsigned default NULL,
`lastname` varchar(50) NOT NULL default '',
`firstname` varchar(50) NOT NULL default '',
`fullname` varchar(127) NOT NULL default '',
`username` varchar(255) NOT NULL, /* Joining by this column */
`email` varchar(255) NOT NULL default '',
`office` varchar(255) NOT NULL default '',
`department` varchar(255) NOT NULL default '',
`telephone` varchar(50) NOT NULL default '',
`organizationalUnit` varchar(255) NOT NULL default '',
`manager_id` int(10) unsigned default NULL,
`sort_key` varchar(51) default NULL,
PRIMARY KEY (`employee_id`),
KEY `employee_lastname_index` (`lastname`),
KEY `employee_sort_key_index` (`sort_key`),
KEY `employee_firstname_index` (`firstname`),
KEY `employee_username_index` (`username`), /* Here is the index */
KEY `employee_department_index` (`department`),
KEY `employee_alt_user_id_index` (`alt_user_id`),
KEY `employee_organizationalunit_index` (`organizationalUnit`),
KEY `employee_manager_id_index` (`manager_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3505 DEFAULT CHARSET=latin1
The query is as follows, and as you can see, no index is used (type=ALL).
mysql> explain select e.firstname, u.user_registered from wp_users u
left outer join vpw_vp_employee e on (u.user_login = e.username);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 656 | |
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 3461 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
I tried adding "use index (user_login_key)" after the first table and "use index (employee_username_index) after the second, with no change. However, if change the outer join to an inner join, the index is used.
I located this mySQL bug that seems similar, but I don't know if this is the cause or not:
http://bugs.mysql.com/bug.php?id=11945.
Can anyone please explain why this happens, and how to get the index to be used?
Thank you very much.