From: Eric Bergen
Date: September 4 2004 1:36am
Subject: Re: please explain why this query isn't optimized
List-Archive: http://lists.mysql.com/mysql/172083
Message-Id: <11b1bd99040903183672226f38@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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 <donny@stripped> 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: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=donny@stripped
> 
> 
> >
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=eric.bergen@stripped
> 
> 



-- 
Eric Bergen
eric.bergen@stripped