MySQL Forums
Forum List  »  Performance

Why does my Left Outer Join not use an index?
Posted by: Daniel Barrett
Date: March 05, 2012 11:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Why does my Left Outer Join not use an index?
15412
March 05, 2012 11:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.