We're using MySql 5.5.37, Java 7 and Hibernate 5.1.3. Hibernate is auto-generating queries and there is one that is confusing me. We have these tables ...
CREATE TABLE `user` (
`ID` varchar(32) COLLATE utf8_bin NOT NULL,
`FIRST_NAME` varchar(50) COLLATE utf8_bin NOT NULL,
`MIDDLE_NAME` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`LAST_NAME` varchar(50) COLLATE utf8_bin NOT NULL,
`USER_NAME` varchar(50) COLLATE utf8_bin NOT NULL,
`URL` varchar(200) COLLATE utf8_bin NOT NULL,
`SALUTATION` varchar(10) COLLATE utf8_bin DEFAULT NULL,
...
`ADDRESS_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `USER_IDX_01` (`USER_NAME`,`URL`),
KEY `FK2_USER` (`GRADE_ID`),
KEY `FK4_USER` (`USER_DEMOGRAPHIC_INFO_ID`),
KEY `FK3_USER` (`CREATOR_ID`),
KEY `FK_USER` (`ADDRESS_ID`),
CONSTRAINT `FK2_USER` FOREIGN KEY (`GRADE_ID`) REFERENCES `grade` (`ID`),
CONSTRAINT `FK3_USER` FOREIGN KEY (`CREATOR_ID`) REFERENCES `user` (`ID`) ON UPDATE NO ACTION,
CONSTRAINT `FK_USER` FOREIGN KEY (`ADDRESS_ID`) REFERENCES `cb_address` (`ID`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
and
role | CREATE TABLE `role` (
`ID` varchar(40) COLLATE utf8_bin NOT NULL,
`NAME` varchar(40) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ROLE_IDX_01` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
and we join them together with a join table
user_role | CREATE TABLE `user_role` (
`USER_ID` varchar(32) COLLATE utf8_bin NOT NULL,
`ROLE_ID` varchar(40) COLLATE utf8_bin NOT NULL,
UNIQUE KEY `USER_ROLE_IDX_02` (`USER_ID`,`ROLE_ID`),
KEY `FK2_USER_ROLE` (`ROLE_ID`),
CONSTRAINT `FK1_USER_ROLE` FOREIGN KEY (`USER_ID`) REFERENCES `user` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `FK2_USER_ROLE` FOREIGN KEY (`ROLE_ID`) REFERENCES `role` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
But when we query for a user and include the roles in our query, MySql seems to be ignoring the index. Notice the "1688568" rows in the explain plan. Why isn't MySQL using the index and what would be a better way of querying the data?
mysql> EXPLAIN select user0_.id as id1_112_, user0_.ADDRESS_ID as ADDRESS17_112_,
user0_.AVATAR as AVATAR2_112_, user0_.CREATED_ON as CREATED_3_112_,
user0_.CREATOR_ID as CREATOR18_112_, user0_.DOB as DOB4_112_,
user0_.ENABLED as ENABLED5_112_, user0_.EXPIRATION as EXPIRATI6_112_,
user0_.first_name as first_na7_112_, user0_.GRADE_ID as GRADE_I19_112_,
user0_.INCORRECT_LOGINS as INCORREC8_112_, user0_.last_name as last_nam9_112_,
user0_.middle_name as middle_10_112_, user0_.password as passwor11_112_,
user0_.RESET_STATE as RESET_S12_112_, user0_.salutation as salutat13_112_,
user0_.temporary_password as tempora14_112_, user0_.url as url15_112_,
user0_.USER_DEMOGRAPHIC_INFO_ID as USER_DE20_112_, user0_.user_name as user_na16_112_
from user user0_
inner join user_role roles1_ ON user0_.id = roles1_.USER_ID
inner join role role2_ ON roles1_.ROLE_ID = role2_.ID
inner join cb_address address3_ ON user0_.ADDRESS_ID = address3_.id
where user0_.url = 'mycityst.myco.org'
and (role2_.ID in ('Student'))
and lower(address3_.email) = '[email protected]'
and user0_.ENABLED = 1;
+----+-------------+-----------+--------+--------------------------------------+------------------+---------+---------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+--------------------------------------+------------------+---------+---------------------------+---------+--------------------------+
| 1 | SIMPLE | role2_ | const | PRIMARY | PRIMARY | 122 | const | 1 | Using index |
| 1 | SIMPLE | roles1_ | ref | USER_ROLE_IDX_02,FK2_USER_ROLE | FK2_USER_ROLE | 122 | const | 1688568 | Using where; Using index |
| 1 | SIMPLE | user0_ | eq_ref | PRIMARY,FK_USER | PRIMARY | 98 | schema1.roles1_.USER_ID | 1 | Using where |
| 1 | SIMPLE | address3_ | eq_ref | PRIMARY | PRIMARY | 98 | schema1.user0_.ADDRESS_ID | 1 | Using where |
+----+-------------+-----------+--------+--------------------------------------+------------------+---------+---------------------------+---------+--------------------------+
Apart from the joins via the primary keys, you have no index that is usable for your search condition except the term role2_.ID in ('Student')). So MySQL takes that term, starts at the table roles1_ (table user_roles) (via the join-relation role2_.id = roles1_.role_id) looking for all students using the index FK2_USER_ROLE (and estimating to get about 1.6 millions rows in that table), then joins the other tables via the primary keys.
For a search for
where user0_.url='mycityst.myco.org'
and (role2_.ID in ('Student'))
and lower(address3_.email)='[email protected]'
and user0_.ENABLED=1;
you are missing a usable key on user0_.url (you only have an index on (USER_NAME,URL) in that table), and a potential index on address3_.email cannot be used because of the lower()-function. (The description of your addresstable is missing though, so it is unclear if you have an index at all) .
So as a fast fix, add an index for user0_(url) (maybe including enabled).
You should also reconsider the use of utf8_bin. It regards 'A' different from 'a'. So it prevents you from using an index if you actually want to do a case insensitive search, which you will usually want to do for emails, names, addresses or urls. The use of a function like lower(email) prevents the use of an index on that column too. So if at all possible, replace your columns with a case insensitive collation (e.g. utf8_unicode_ci, the ci stands for case insensitive).