List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 11:30pm
Subject:Strange feeature
View as plain text  
>>>>> "Mark" == Mark Papadakis <markp@stripped> writes:

Mark> Hi!
Mark> This have been happening for quite some time. Consider the case where we have a
> table like this:

Mark> CREATE TABLE gbheader (
Mark>   id bigint(20) unsigned NOT NULL auto_increment,
Mark>   owner int(10) unsigned DEFAULT '0' NOT NULL,
Mark>   host varchar(50),
Mark>   agent varchar(80),
Mark>   dt int(10) unsigned,
Mark>   approved tinyint(3) unsigned,
Mark>   PRIMARY KEY (id),
Mark>   UNIQUE key2 (owner,id)
Mark> );   

Mark> select id from gbheader where owner=x order by id limit 1;

Mark> If we issue this it is blizzing fast but if we use something like
Mark> select * from gbheader where owner=x order by id limit 1; 
Mark> it takes a LONG time.
Mark> ( there are over 11.000 records where owner=x )

Mark> It seems as like it reads all data from gbheader where owner=x then sorts them
> using the id ( i.e doesn't use the key )

Mark> The explain shows that the key2 is used.

Key 2 can't be used for ORDER BY as MySQL isn't yet smart enough to
understand that 'owner' is a constant in this case.

You can however force MySQL to use the second key for ORDER BY by using:

select * from gbheader where owner=x order by owner,id limit 1; 

Mark> This happens in many other cased with similar table structure. I have tested this
> with mysql3.23.3, mysql3.23.4, mysql 3.22.5 but it works the same.

Mark> To summarize, it seems that sometimes the index is not used although the explain
> indicates that is used.

EXPLAIN only tells that the index is used to retrieve the rows.  It
doesn't tell you anything about how the sort is done (we have some
ideas to fix the output of EXPLAIN for cases like this)

Strange feeatureMark Papadakis12 Oct
  • Strange feeatureMichael Widenius13 Oct