In the last episode (Sep 03), Dave Dyer said:
> At 05:39 PM 9/3/2004, Donny Simonton wrote:
> >It would help if you would say how many entries do you have for
> >changed =0 and how many are greater than 0.
> >
> >Since changed is a timestamp you should never get an entry of 0. So
> >the query of changed>0 will always do a full table scan. This is
> >definitely not a bug.
>
> Since I am asking for the max value, and there is an index on that
> value, I see no reason why all the entries should be scanned.
Well, since the Extra field on the first query says "Using index", it
isn't doing a full table scan, it's just walking the index. A full
table scan would probably have taken even longer.
MySQL keeps track of the max and min values for all indexed columns,
which is why the table was optimized away for your simpler query. It
looks like MySQL thinks that your WHERE clause could be affecting the
results, so it needs to walk the index. You didn't say which version
of MySQL you're using (which is very important when asking optimization
questions like this), but MySQL 4.0.x seems to behave this way.
> Getting the same answer, from a simpler query, in infinitely
> less time, just seems wrong to me.
Makes perfect sense. Simpler queries *are* easier to optimize, you
know :) MySQL 4.1 does try to use the precomputed MAX value, but it's
too aggressive. I was trying to reproduce your results and discovered
this:
SELECT VERSION();
+-----------------------+
| VERSION() |
+-----------------------+
| 4.1.4-gamma-debug-log |
+-----------------------+
CREATE TABLE foo (bar int primary key);
INSERT INTO foo VALUES (1),(2),(3),(4);
SELECT MAX(bar) FROM foo WHERE bar > 5;
+----------+
| MAX(bar) |
+----------+
| 4 |
+----------+
SELECT * FROM foo WHERE bar > 5;
Empty set (0.00 sec)
I've filed MySQL bug 5406 on this issue.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
