List:General Discussion« Previous MessageNext Message »
From:Dave Dyer Date:September 4 2004 1:42am
Subject:RE: please explain why this query isn't optimized
View as plain text  
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.


Thread
please explain why this query isn't optimizedDave Dyer4 Sep
  • RE: please explain why this query isn't optimizedDonny Simonton4 Sep
    • Re: please explain why this query isn't optimizedEric Bergen4 Sep
    • RE: please explain why this query isn't optimizedDave Dyer4 Sep
      • Re: please explain why this query isn't optimizedDan Nelson4 Sep
        • Re: please explain why this query isn't optimizedDave Dyer4 Sep
          • RE: please explain why this query isn't optimizedDonny Simonton4 Sep
  • Re: please explain why this query isn't optimizedEgor Egorov6 Sep
Re: please explain why this query isn't optimizedBill Easton6 Sep
  • Re: please explain why this query isn't optimizedDavid T-G7 Sep
    • Re: please explain why this query isn't optimizedEgor Egorov7 Sep
  • Re: please explain why this query isn't optimizedEgor Egorov7 Sep