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:[EMAIL PROTECTED] > Sent: Friday, September 03, 2004 7:04 PM > To: [EMAIL PROTECTED] > 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
