Hi Aftab Thanks for your prompt answer.
Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE INDEX as what we really need to do are DELETE operations, and it seems FORCE INDEX is valid only for SELECT statements. Basically we have a huge database where we track different operations and every day we need to do some maintenance and delete records which are older than specific date. Thx Javier -----Original Message----- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:03 To: Javier Diaz Subject: Re: Problems with indexes on Date/DateTime fields Is not the second quary doing a full table scan? The parser may find this better than using the Index. --- Javier Diaz <[EMAIL PROTECTED]> wrote: > Hi all > > We are having a weird problem with some queries > which are not using some > indexes in date fields. > > Query-1 > SELECT [field list] FROM tableX > WHERE dateField = [any date expression or constant > value] > > Query-2 > SELECT [field list] FROM tableX > WHERE dateField <= [any date expression or constant > value] > > > We have an index for dateField in tableX. However > Query-1 is using the > index but Query-2 is not. We have tested a lot of > different combinations > and every time we use any of these operators <=, <, > >, >= > > Mysql stop using the index. We are using Mysql > 4.1.12 binary > distribution running on linux. > > Any help will be really appreciated. > > Thanks > Javier > > > ********************************************************************** > This email and any files transmitted with it are > confidential and > intended solely for the use of the individual or > entity to whom they > are addressed. If you have received this email in > error please notify > the system manager. > > This footnote also confirms that this email message > has been swept by > MIMEsweeper for the presence of computer viruses. > > www.mimesweeper.com > ********************************************************************** > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________________ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
