MySQL Forums
Forum List  »  Optimizer & Parser

Index not working for date field used in between query
Posted by: Shrinivasa rao
Date: July 22, 2015 11:48PM

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.5and above. Please suggest.

Options: ReplyQuote


Subject
Views
Written By
Posted
Index not working for date field used in between query
5302
July 22, 2015 11:48PM


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.