Same mysql query runs faster on 4.0 than on 4.1?

[gr8amol]

I have a mysql database on server 4.0 which has a table with the following column:
`id` varchar(127) binary NOT NULL default ''

When I try copying the table onto a newer server 4.1 then the same column looks like this:
`id` varchar(127) character set latin1 collate latin1_bin NOT NULL default ''

As a result of this, the indexes also differ:

on 4.0
Table
Non_unique
Key_name
Seq_in_index
Column_name
Collation
Cardinality
Sub_part
Packed
Null
Index_type
Comment

mytable
1
t_id
1
id
A
NULL
NULL
NULL
BTREE

on 4.1
mytable
1
h_id
1
id
A
16187
NULL
NULL
BTREE

NOTE the change in the cardinality value: NULL (on 4.0) vs 16187 (on 4.1)
I think it has to do something with the collation and the character_set, but I tried changing their values and it didn't work.

Any help would be greatly appreciated!

Let me know if I should post any additional info to make my case clear.

Thanks.
Thanks for your replies....
Both of them are MYISAM tables..
Here is the show create table on the 2 tables:
On 4.1
CREATE TABLE `mytable` (
`id` varchar(127) character set latin1 collate latin1_bin NOT NULL default '',
`object` varchar(127) NOT NULL default '',
`old_status` varchar(127) NOT NULL default '',
`transition` varchar(127) NOT NULL default '',
`new_status` varchar(127) NOT NULL default '',
`ts` int(11) NOT NULL default '0',
`current` tinyint(4) default NULL,
`maintainer` varchar(63) default NULL,
KEY `h_id` (`id`),
KEY `h_id_obj` (`id`,`object`),
KEY `h_old_status` (`old_status`),
KEY `h_trans` (`transition`),
KEY `h_new_status` (`new_status`),
KEY `h_timestamp` (`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `mytable` (
`id` varchar(127) binary NOT NULL default '',
`object` varchar(127) NOT NULL default '',
`old_status` varchar(127) NOT NULL default '',
`transition` varchar(127) NOT NULL default '',
`new_status` varchar(127) NOT NULL default '',
`ts` int(11) NOT NULL default '0',
`current` tinyint(4) default NULL,
`maintainer` varchar(63) default NULL,
KEY `h_id` (`id`),
KEY `h_id_obj` (`id`,`object`),
KEY `h_start_status` (`old_status`),
KEY `h_trans` (`transition`),
KEY `h_finish_status` (`new_status`),
KEY `h_timestamp` (`ts`)
) TYPE=MyISAM
The 2nd one being on 40
The 2nd one being on 4.0
Scott: How do I import the data without using mysqldump from one server to the other?

[dhvrm]

Seeing how you're using the same default collation and character sets that are implicit in 4.0, I don't think those are factors.

If you are using InnoDB in one database and MyISAM in another, that would have an impact.

When in doubt, RTM.

http://dev.mysql.com/doc/refman/4.1/en/optimization.html

[Scott P]

Instead of coping, will you do import data? Import data should keep every thing the same.