--- "Keith C. Ivey" <[EMAIL PROTECTED]> wrote: > On 21 Nov 2002, at 18:20, Hans Zaunere wrote: > > > > 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. > > Any time you're looking for the first part of a column value, you're > looking for a range. It's simply a special case of a range query. I > don't see why you care what MySQL calls it, as long as you're getting > an answer in a reasonable time.
I'm not... sure, in development it's acceptable but with tens of thousands of rows it won't be. I know all about cacheing and all, but I'm just frustrated by this DATETIME column issue, after switching from the fantastic INT column and UNIX timestamp scheme. > > 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. > > Maybe Oracle and MySQL are doing the same thing behind the curtain, > or maybe they're not. I think you're obsessing too much about the > type in the EXPLAIN output. The EXPLAIN output is just a diagnostic > tool, not an end in itself. > > The important question is whether you're getting the correct result > fast enough for your purposes. No, I'm not, and I need to squeeze what I can from this query. > If so, then stop worrying. If not, > then you may need to make some changes in your table structure > (introduce a separate DATE column, or break the date and time into > different columns). Yeah, certainly possible, but it seems backwards to have two columns, when DATETIME is built for just the type of data I need. I guess what throws me is this from http://www.mysql.com/doc/en/Comparison_Operators.html "If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly." ODBC or not, this is very important feature of a DATE-centric column, otherwise, what's the sense of a special column, except a larger range than an int (which could be circumvented anyway). And since MySQL is doing the conversion as noted above, which is good, there's no reason it shouldn't recognize that "2002-11-27" means a "wildcard" or "unimportant" time, and only pay attention to the date part. Is there a "feature request" type of list or forum where one can talk directly to developers, etc. ? > Yes, it might be nice if you could do something like "WHERE > DATE_PART(thecolumn) = '2002-11-21'" and have MySQL use the index, > but it would be doing exactly the same thing it does now when you say > "WHERE thecolumn BETWEEN '2002-11-21 00:00:00' AND '2002-11-21 > 23:59:59'". There wouldn't be any difference in performance, just a > different query syntax. > > But maybe I'm still misunderstanding your problem. Again, no - it's not just syntactic sugar. A range comparison isn't as fast, or the same, as a direct comparison, ever! 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
