In the event that mysql has to scan the entire table it will ignore the index because it's faster to read straight through the datafile than it is to seek on the index. -Eric
On Fri, 3 Sep 2004 19:39:38 -0500, Donny Simonton <[EMAIL PROTECTED]> 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. > > 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] > > -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
