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.
Donny
> -----Original Message-----
> From: Dave Dyer [mailto:ddyer@stripped]
> Sent: Friday, September 03, 2004 7:04 PM
> To: mysql@stripped
> Subject: please explain why this query isn't optimized
>
>
> Before I post it as a bug, perhaps someone can explain why
> this query is not optimized to use the index (it has to examine all 287k
> rows).
>
> mysql> explain SELECT MAX(changed) FROM archived_stats where changed>0;
> +----------------+-------+---------------+---------+---------+------+-----
> ----+--------------------------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
> +----------------+-------+---------------+---------+---------+------+-----
> ----+--------------------------+
> | archived_stats | range | changed | changed | 4 | NULL |
> 2878820 | Using where; Using index |
> +----------------+-------+---------------+---------+---------+------+-----
> ----+--------------------------+
>
> whereas this query is optimized:
>
> mysql> explain SELECT MAX(changed) FROM archived_stats;
> +------------------------------+
> | Comment |
> +------------------------------+
> | Select tables optimized away |
> +------------------------------+
>
>
> The table in question:
>
> mysql> describe archived_stats;
> +--------------+---------------+------+-----+---------------------+-------
> +
> | Field | Type | Null | Key | Default | Extra
> |
> +--------------+---------------+------+-----+---------------------+-------
> +
> | number | char(32) | | MUL | |
> |
> | bad_login | int(11) | | | 0 |
> |
> | good_login | int(11) | | | 0 |
> |
> | last_login | timestamp(14) | YES | | NULL |
> |
> | batch_flow | int(11) | | | 0 |
> |
> | upload_image | int(11) | | | 0 |
> |
> | page_proof | int(11) | | | 0 |
> |
> | process_form | int(11) | | | 0 |
> |
> | changed | timestamp(14) | YES | MUL | 00000000000000 |
> |
> | sync_date | datetime | YES | | 0000-00-00 00:00:00 |
> |
> +--------------+---------------+------+-----+---------------------+-------
> +
> 10 rows in set (0.03 sec)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
