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]
