Problem with indexes
I have run into a weird problem in our test migrating from mysql 4.1 myisam tables to mysql 5 ndb tables. I have one table that has in index on a column as well as the column being part of another index (active).
mysql> show create table membership\G
*************************** 1. row ***************************
Table: membership
Create Table: CREATE TABLE `membership` (
`organization_id` int(10) unsigned NOT NULL default '0',
`user_id` int(10) unsigned NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`department` varchar(255) NOT NULL default '',
`phone_extension` varchar(255) NOT NULL default '',
`phone_extension_exclude` char(1) NOT NULL default '',
`email_address` varchar(255) NOT NULL default '',
`email_address_exclude` char(1) NOT NULL default '',
`member_username` varchar(255) NOT NULL default '',
`member_password` varchar(255) NOT NULL default '',
`org_phone` varchar(255) NOT NULL default '',
`org_phone_exclude` char(1) NOT NULL default '',
`org_fax` varchar(255) NOT NULL default '',
`org_fax_exclude` char(1) NOT NULL default '',
`org_cell` varchar(255) NOT NULL default '',
`org_cell_exclude` char(1) NOT NULL default '',
`org_pager` varchar(255) NOT NULL default '',
`role` varchar(255) NOT NULL default '',
`assistant` int(10) unsigned NOT NULL default '0',
`active` char(1) NOT NULL default 'Y',
`remote_key_mem` varchar(255) NOT NULL default '',
PRIMARY KEY (`organization_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `email_address` (`email_address`),
KEY `organization_id` (`organization_id`,`active`),
KEY `active` (`active`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
This query should be returning results:
mysql> select organization_id, user_id, active from membership where organization_id = 23 and active = 'Y' limit 1\G
Empty set (0.05 sec)
As seen by this query (using like with at % at the begining to force it not to use an index)
mysql> select organization_id, user_id, active from membership where organization_id = 23 and active like '%Y%' limit 1\G
*************************** 1. row ***************************
organization_id: 23
user_id: 134
active: Y
1 row in set (0.04 sec)
I also made sure there were no extra spacing around active even though it is a CHAR(1) with:
mysql> select length(active) from membership where user_id=134;
+----------------+
| length(active) |
+----------------+
| 1 |
+----------------+
1 row in set (0.05 sec)
If I drop either the active index or (`organization_id`,`active`) index, queries work exactly as expected. I upgraded to 5.0.13 a bit earlier today and am still seeing the same problem. Any ideas on a fix for this?