MySQL Forums
Forum List  »  Optimizer & Parser

Re: not able to use index, seems optimizer forces not to use index,
Posted by: Rick James
Date: September 22, 2011 03:30PM

> where occurance > '20110501' and occurance < '20110502';
Did you mean to exclude both midnights? Perhaps you want:
where occurance >= '20110501' and occurance < '20110502';

> where (`security_file`.`nsd_ecomm`.`occurance` > convert(quote(date_format((sysdate() - interval 1 day),
'%Y%m%d')) using latin1))
-->
where (`security_file`.`nsd_ecomm`.`occurance` > sysdate() - interval 1 day)
That is, if occurance is DATETIME or TIMESTAMP (or DATE), then you don't need such complexity.

OK, so you have a funky timestamp.

Drop QUOTE(). You need the quotes only when writing a literal, not in the string itself.

SHOW CREATE TABLE nsd_ecomm \G
I'm guessing occurance is a CHAR or VARCHAR?

How many rows match the WHERE clause? How many rows in the table? If the percentage is too high, the optimizer will skip using the index and simply scan the whole table.

Perhaps you don't need any of the funny business:
mysql> SELECT NOW() > '20110922000000', NOW() < '20110923000000', NOW();
+--------------------------+--------------------------+---------------------+
| NOW() > '20110922000000' | NOW() < '20110923000000' | NOW()               |
+--------------------------+--------------------------+---------------------+
|                        1 |                        1 | 2011-09-22 14:29:50 |
+--------------------------+--------------------------+---------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: not able to use index, seems optimizer forces not to use index,
851
September 22, 2011 03:30PM


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.