List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:September 4 2004 1:36am
Subject:Re: please explain why this query isn't optimized
View as plain text  
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=1
> 
> 
> >
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 



-- 
Eric Bergen
eric.bergen@stripped
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