So essentially, you want to put an index on part of the column, and not the whole column, right? As in, an index on just the date part, and not the time part. (Or both -- it seems like you want it to do both at the same time). Much like you can with a char column -- put a char on the first 2 letters, so that you can return all columns that have those first two letters the same.
So... can you change the DATETIME column to a char[14] column, and then put an index on the first 8 characters (representing your date)? That seems to be what you're looking for -- because then you could search for just the date (the first 8 characters) and it would be a direct ref to all the times under that date. Or am I completely off base here =] Nick Elliott ----- Original Message ----- From: "Hans Zaunere" <[EMAIL PROTECTED]> To: "Keith C. Ivey" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, November 21, 2002 9:20 PM Subject: Re: DATETIME Masking and Comparison > --- "Keith C. Ivey" <[EMAIL PROTECTED]> wrote: > > On 21 Nov 2002, at 15:18, Hans Zaunere wrote: > > > > > Because it seems ineffncient to me, as MySQL must be doing more work to > > > calculate the range. Even EXPLAIN tells me this, by showing that a > > > less-than-optimal TYPE is being used. Although "WHERE thecolumn = > > > '2002-11-17'" doesn't yield any results, EXPLAIN shows that it's using a > > > better index type. > > > > Yes, EXPLAIN says that the type is 'range' rather than 'ref', but > > you're doing a range query. That's the best you can get for your > > query, so there's no point in worrying about what might be optimal > > for a different sort of query. > > But in fact that's not the query I'm doing. It's a range query only because > that's the only way I've found to accomplish what I need - if I could compare > against only the date part, it wouldn't be a range query. > > > The light for searching might be > > optimal under the lamppost rather than in the alley, but if you > > dropped your keys in the alley you have to search for them there. > > Yeah... but my key is in the light, except it's inexplainably invisible. > > > If you never care about the time portion of the column, then it would > > be better to make it a DATE column, > > Of course, but the time is important at times (yeah, I could split it into > different columns, but that's not at issue). > > > but as long as it's a DATETIME > > column then you are looking for a range of values, so 'range' is the > > best you're going to get in the EXPLAIN output. > > Yes, but the real data I'm looking for is not a range - it's simply a part of > a single value. > > In Oracle the DATE column type contains date and time information. However, > one can query only for the date, and Oracle will honor that as what it needs > to pick out of the column. I had hoped that MySQL would do the same, since > it looks like it stores the data in a very similar way. > > Best, > > > > ===== > Hans Zaunere > New York PHP > http://nyphp.org > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
