Re: date(year to second field)

Sukumar Konduru wrote: > > Hi > > I have table with field "year to second". I need to prepare reports > quiet > oftern the transactions on day basis. > > I would like to know which is efficient to use. > > select * from taskresult > where date(startedat) = today> > or > select * from taskresult > where startedat >="1998-04-29 00:00:00" and> startedat <="1998-04-29 59:59:59" You would have to benchmark both to be sure but the second is faster in theory because in the second query the two date strings will each be converted to datetime year to second and each row will be compared natively as a datetime. In the first version every row will have to be converted to type date before comparing to the value of today. (BTW for 7.xx versions prior to 7.13 this does not hold. In early 7.1x versions comparisons between datetime and strings were done by converting each row to a string, 7.13 fixed this.) Factors affecting actual performance include: - the number of rows, for a smaller table the overhead of the repeated conversions may not matter. - whether an index can be used for the second query, the first query cannot use an index for the date comparison and so will do a table scan unless other keys can use indexes. Even then every matching row will have to be fetched and tested (unless a key-only index is available). Note the phrasing; versions before 7.24 WILL NOT use index keys beyond the third indexed column for index filtering and will fetch the datapage anyway (again except for key-only searches). These and other complications mean that you will have to benchmark it yourself. Art S. Kagel