MySQL is using the index in both of those. The first query where changed < 78900000; should be very fast since explain says it only has to find one row. The second query, after mysql does a range scan on the index has to still examine 11551351 rows to find a max value.
The last query SELECT MAX(changed) FROM archived_stats; hits an optimization inside mysql so it can just read the max value out of the index. (MySQL caches min/max/count for myisam tables) -Eric On Fri, 03 Sep 2004 18:42:44 -0700, Dave Dyer <[EMAIL PROTECTED]> wrote: > 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. > > A more interesting and relevant query would use intermediate > values, "select min(changed) where changed>xxx", which would > get me the first entry > xxx. > > mysql> explain SELECT MAX(changed) FROM archived_stats where changed < 78900000; > +----------------+-------+---------------+---------+---------+------+------+--------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +----------------+-------+---------------+---------+---------+------+------+--------------------------+ > | archived_stats | range | changed | changed | 4 | NULL | 1 | Using > where; Using index | > +----------------+-------+---------------+---------+---------+------+------+--------------------------+ > 1 row in set (0.06 sec) > > mysql> explain SELECT MAX(changed) FROM archived_stats where changed > 78900000; > +----------------+-------+---------------+---------+---------+------+----------+--------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +----------------+-------+---------------+---------+---------+------+----------+--------------------------+ > | archived_stats | range | changed | changed | 4 | NULL | 11551351 | > Using where; Using index | > +----------------+-------+---------------+---------+---------+------+----------+--------------------------+ > 1 row in set (0.00 sec) > > mysql> SELECT MAX(changed) FROM archived_stats where changed > 78900000; > +----------------+ > | MAX(changed) | > +----------------+ > | 20040826202123 | > +----------------+ > 1 row in set (3 min 36.02 sec) > > mysql> SELECT MAX(changed) FROM archived_stats ; > +----------------+ > | MAX(changed) | > +----------------+ > | 20040826202123 | > +----------------+ > 1 row in set (0.00 sec) > > Getting the same answer, from a simpler query, in infinitely > less time, just seems wrong to me. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Eric Bergen [EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
