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



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