Not the answer you need?
Register and ask your own question!

Index not working for date field used in between query

ikonraoikonrao ContributorCurrent User Role Advisor
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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.