Index differences between the statemens "equal" and "between" in left join

With the following query i noticed that the index is not used correctly (the same problem happens even with 'USE INDEX, FORCE INDEX') ANALYZE SELECT TABLE1.id,TABLE1.data,TABLE2.id,TABLE2.data FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.id BETWEEN TABLE1.id*10000+1 AND TABLE1.id*10000+9999

With the following query i noticed that the index is used correctly ANALYZE SELECT TABLE1.id,TABLE1.data,TABLE2.id,TABLE2.data FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.id=TABLE1.id*10000+1

why is it like that?

this is the schema of my database CREATE TABLE IF NOT EXISTS `table1` ( `Id` int(11) NOT NULL, `Data` varchar(50) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

REPLACE INTO `table1` (`Id`, `Data`) VALUES (1, 'Test 1'); REPLACE INTO `table1` (`Id`, `Data`) VALUES (2, 'Test 2'); REPLACE INTO `table1` (`Id`, `Data`) VALUES (3, 'Test 3'); REPLACE INTO `table1` (`Id`, `Data`) VALUES (4, 'Test 4');

CREATE TABLE IF NOT EXISTS `table2` ( `Id` int(11) NOT NULL, `Data` varchar(50) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

REPLACE INTO `table2` (`Id`, `Data`) VALUES (10001, 'Test 1/1'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (10002, 'Test 1/2'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (10003, 'Test 1/3'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (20001, 'Test 2/1'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (20002, 'Test 2/2'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (30001, 'Test 3/1'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (30002, 'Test 3/2'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (40001, 'Test 4/1'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (40002, 'Test 4/2'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (40003, 'Test 4/3'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (40004, 'Test 4/4'); REPLACE INTO `table2` (`Id`, `Data`) VALUES (40005, 'Test 4/5');