Re: MySQL uses PRIMARY key when it should? use another index

[Marko Mäkelä]

On Wed, Jan 02, 2008 at 07:15:24AM -0800, Rick James wrote:
> I would be surprised if using t1ba is ever the 'right' way to do the
> query.  The "(10)" virtually destroys any usefullness of the index.  And
> since you asked for "a,b", it could really be satisfied in the index.  I
> do find it puzzling that it says both "PRIMARY" and "Using index".

Right, the test may have succeeded in the past because of a MySQL bug that
has been fixed.  But we would like to know where this change was made.

There still seem to be bugs in fast index creation.  For example, this
test case results in a table copy, even for a MyISAM table:

create table t1(a int, b varchar(255), primary key(a,b))
engine=myisam default charset=utf8;
create index t1ba on t1(b,a);

Somehow, this test in compare_tables() on column b is failing:

    /* Evaluate changes bitmap and send to check_if_incompatible_data()
    */
    if (!(tmp= field->is_equal(new_field)))
    {
       *need_copy_table= ALTER_TABLE_DATA_CHANGED;
       DBUG_RETURN(0);
    }

The culprit seems to be the "default charset=utf8".  The is_equal test
does succeed with the default charset of latin1.

	Marko