MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index not working for date field used in between query
Posted by: Rick James
Date: July 26, 2015 08:28AM

The range is 15 years? That covers most (or all?) of the table? Then it is actually inefficient to use the index; it is faster to simply scan the entire table and toss the few rows that don't apply.

I am unclear what "bug" you are alluding to. Shunning an index has always been a deliberate optimization, as hinted at above.

The optimal INDEX for that query would be
INDEX(iname, createDate)

Other comments:

PRIMARY KEY (`userId`),
UNIQUE KEY `IX_5ADBE171` (`contactId`),
Why have two BIGINTs to reach the same row?

UNIQUE KEY `IX_9782AD88` (`companyId`,`userId`),
Since userId is UNIQUE, there is no advantage in making this pair UNIQUE.

KEY `IX_3A1E834E` (`companyId`),
This index is covered by 5 other indexes; it can be DROPped without loss of functionality.

`uuid_` varchar(75) DEFAULT NULL,
KEY `IX_E0422BDA` (`uuid_`),
Is that a standard 36-char UUID? Or something that really needs up to 75 characters? Why have it at all if you have `userId` and `contactId`?

Longer than necessary for many fields. This slows down _some_ queries in obscure ways.

Are most of the fields really NULLable? Or should the be "NOT NULL"?

Is innodb_buffer_pool_size somewhere around 70% of available RAM?

Options: ReplyQuote

Written By
Re: Index not working for date field used in between query
July 26, 2015 08:28AM

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.