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

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