Description:
A customer posted on the Cluster List that he was having issues getting NDB to use the indexes he provided, but InnoDB would use them without issues. I have recreated his issues and will attach test case and data files to this bug report. In addition I tried the test with ndb_use_exact_count=off and still did not get index seen in the explain.
+ CREATE TABLE `t1` (
+ `id` int(11) NOT NULL auto_increment,
+ `target_id` int(11) NOT NULL default '0',
+ `end_user_operation_id` int(11) NOT NULL default '0',
+ `user_id` int(11) NOT NULL default '0',
+ `carrier_id` int(11) NOT NULL default '0',
+ `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
+ `target_type_id` int(11) NOT NULL default '0',
+ `status` int(2) NOT NULL default '0',
+ `interface_type` int(2) NOT NULL default '0',
+ `interface_code` int(11) default NULL,
+ `la_id` int(11) default NULL,
+ `parent_id` int(11) default NULL,
+ PRIMARY KEY (`id`),
+ KEY `eua_carrier_id_target_id_timestamp`
+ (`timestamp`,`carrier_id`,`target_id`),
+ KEY `eua_user_id_timestamp` (`user_id`,`timestamp`),
+ KEY `eua_timestamp` (`timestamp`)
+ ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
+ CREATE TABLE `t2` (
+ `id` int(11) NOT NULL auto_increment,
+ `target_id` int(11) NOT NULL default '0',
+ `end_user_operation_id` int(11) NOT NULL default '0',
+ `user_id` int(11) NOT NULL default '0',
+ `carrier_id` int(11) NOT NULL default '0',
+ `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
+ `target_type_id` int(11) NOT NULL default '0',
+ `status` int(2) NOT NULL default '0',
+ `interface_type` int(2) NOT NULL default '0',
+ `interface_code` int(11) default NULL,
+ `la_id` int(11) default NULL,
+ `parent_id` int(11) default NULL,
+ PRIMARY KEY (`id`),
+ KEY `eua_carrier_id_target_id_timestamp`
+ (`timestamp`,`carrier_id`,`target_id`),
+ KEY `eua_user_id_timestamp` (`user_id`,`timestamp`),
+ KEY `eua_timestamp` (`timestamp`)
+ ) ENGINE=innodb DEFAULT CHARSET=latin1;
+ explain select id from t1 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00';
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+ explain select id from t1 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00';
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+ explain select id from t2 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00';
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 SIMPLE t2 range eua_carrier_id_target_id_timestamp,eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index
+ explain select id from t2 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00';
+ id select_type table type possible_keys key key_len ref rows Extra
+ 1 SIMPLE t2 range eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index
How to repeat:
See attachments
