List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:September 4 2004 1:55am
Subject:Fwd: please explain why this query isn't optimized
View as plain text  
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 <ddyer@stripped> 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/mysql?unsub=1
>
>


--
Eric Bergen
eric.bergen@stripped




-- 
Eric Bergen
eric.bergen@stripped
Thread
Fwd: please explain why this query isn't optimizedEric Bergen4 Sep