"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 11/22/2005 10:37:00 AM:
> (I used to have separate date/time cols. in all tables > but changed them to datetime and buggered up some stuff. > Now I'm trying to find the best way to fix this.) > > If I have an indexed datetime column (`date`), and say: > > select date, <other_cols> > from table1 > where date between '2005-08-01' and '2005-08-14' > order by date; > > Can the index ever be used in a date-only where clause > or must I include the time part also? > Explain does show the index in possible_keys, but is not > shown under key. (Very little data in this table). > > Thanks, > Jon > Depending on how little data there is, it may be less efficient to use the index to get the data if you are selecting more than about 30% of the available rows. Yes, you can use date-only terms in your where clauses but you have to remember that if you list just a date (like '2005-08-01' it will compare to a datetime value as '2005-08-01 00:00:00'. That means that your WHERE clause above is evaluated as WHERE date between '2005-08-01 00:00:00' and '2005-08-14 00:00:00' Unless an event happens exactly on midnight of the 14th, it won't make the cut. Does that explain why you are chopping off the last day's worth of data? Either compare against the next day or the next day minus 1 second WHERE date between '2005-08-01 00:00:00' and '2005-08-15' - INTERVAL 1 SECOND There are many ways to make that adjustment, use whichever works for your versionhttp://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
