Re: Good Informix bug found

Robert Minter wrote: > > * This is one of my favorite Informix bugs I have found in a while.... > * > * I have a LARGE database table with about 10+ million rows. ne > * of the fields in the table is a date field and will always have a value. > * > * If this field is indexed (non-unique) or part of a unique index > * > * you run the following two queries > * > * select count(*) from table where table.date_field = "8/4/96" > * > * or > * > * select count(*) from table where table.date_field = today > * > * the SECOND query using TODAY (or today -1 to get to the date needed)... > * is almost THREE TIMES FASTER. > > What happens when you change the first one to: > > select count(*) from table where table.date_field = DATE("8/4/96")> > Thanks, > > Any type of conversion will delay the query considerably. For example, if you are comparing a smallint field with a string of numeric digits (i.e. WHERE Numeric_field = "12345"), the query would take 5 to 10 times longer than comparing data of the same type. When you are comparing a date field with a date string, Informix must first convert the string to a date value before comparing thee two. I do not believe this is a bug. The delay is simply due to the fact that Informix is doing a lot of work. Gabriel Beccar-Varela E-Mail gabriel@emf.net -----