Thanks Aftab We already think of that option as possible solution but I was just wondering why is Mysql changing the logic when the operator changes.
I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the "=" operator, because if that is the case we will need to re-visit a few queries .... Thx Javier -----Original Message----- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:25 To: Javier Diaz Subject: RE: Problems with indexes on Date/DateTime fields I agree. Some time full table scan is faster than using the index and going row by row. Any way, try using a subquery and get the result on to a temp table or just del usnig it . Some thing like this: Del from table where Key field in (Sel Key from tabl1 where stored date>some date); --- Javier Diaz <[EMAIL PROTECTED]> wrote: > 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 > > __________________________________________ 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]
