# Index not working for date field used in between query

**URL:** <https://forums.percona.com/t/index-not-working-for-date-field-used-in-between-query/4316>\
**Category:** Other MySQL® Questions\
**Created:** [July 23, 2015, 12:21am UTC](https://forums.percona.com/t/index-not-working-for-date-field-used-in-between-query/4316 "2015-07-23T00:21:40Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ikonrao](https://avatars.discourse-cdn.com/v4/letter/i/a9adbd/32.png) [@ikonrao](https://forums.percona.com/u/ikonrao)\
**Post date:** [July 23, 2015, 12:21am UTC](https://forums.percona.com/t/index-not-working-for-date-field-used-in-between-query/4316/1 "2015-07-23T00:21:40Z")

</div>

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.