Hello,
We have MySQL 5.6.21 enterprise edition.
For below query we added index on createdate field, but even after adding index query plan shows that it is not using it.
Query: select iname db1.User where iname like ‘0012%’ and createDate between '2000-05
-11 00:00:00’ and ‘2015-07-01 23:59:59’;
Added index on : createdate
Table structure:
CREATE TABLE User (
uuid_ varchar(75) DEFAULT NULL,
userId bigint(20) NOT NULL,
companyId bigint(20) DEFAULT NULL,
createDate datetime DEFAULT NULL,
modifiedDate datetime DEFAULT NULL,
defaultUser tinyint(4) DEFAULT NULL,
contactId bigint(20) DEFAULT NULL,
password_ varchar(75) DEFAULT NULL,
passwordEncrypted tinyint(4) DEFAULT NULL,
passwordReset tinyint(4) DEFAULT NULL,
passwordModifiedDate datetime DEFAULT NULL,
digest varchar(255) DEFAULT NULL,
reminderQueryQuestion varchar(75) DEFAULT NULL,
reminderQueryAnswer varchar(75) DEFAULT NULL,
graceLoginCount int(11) DEFAULT NULL,
iName varchar(75) DEFAULT NULL,
emailAddress varchar(75) DEFAULT NULL,
facebookId bigint(20) DEFAULT NULL,
openId varchar(1024) DEFAULT NULL,
portraitId bigint(20) DEFAULT NULL,
languageId varchar(75) DEFAULT NULL,
timeZoneId varchar(75) DEFAULT NULL,
greeting varchar(255) DEFAULT NULL,
comments longtext,
firstName varchar(75) DEFAULT NULL,
middleName varchar(75) DEFAULT NULL,
lastName varchar(75) DEFAULT NULL,
jobTitle varchar(100) DEFAULT NULL,
loginDate datetime DEFAULT NULL,
loginIP varchar(75) DEFAULT NULL,
lastLoginDate datetime DEFAULT NULL,
lastLoginIP varchar(75) DEFAULT NULL,
lastFailedLoginDate datetime DEFAULT NULL,
failedLoginAttempts int(11) DEFAULT NULL,
lockout tinyint(4) DEFAULT NULL,
lockoutDate datetime DEFAULT NULL,
agreedToTermsOfUse tinyint(4) DEFAULT NULL,
active_ tinyint(4) DEFAULT NULL,
PRIMARY KEY (userId),
UNIQUE KEY IX_C5806019 (companyId,iName),
UNIQUE KEY IX_9782AD88 (companyId,userId),
UNIQUE KEY IX_5ADBE171 (contactId),
KEY IX_3A1E834E (companyId),
KEY IX_5204C37B (companyId,active_),
KEY IX_6EF03E4E (companyId,defaultUser),
KEY IX_1D731F03 (companyId,facebookId),
KEY IX_89509087 (companyId,openId(767)),
KEY IX_762F63C6 (emailAddress),
KEY IX_A18034A4 (portraitId),
KEY IX_E0422BDA (uuid_),
KEY IX_CDATE (createDate)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
QUERY PLAN after adding index:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: User_
type: ALL
possible_keys: IX_CDATE
key: NULL
key_len: NULL
ref: NULL
rows: 1118483
Extra: Using where
I believe this was a bug earlier and was fixed in 5.5 and above. Please suggest.