ask a question about between index
Table t_tree:
CREATE TABLE `t_tree` (
`NID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK: Node-ID',
`Flags` set('hidden','activ') DEFAULT NULL COMMENT 'Flags',
`Name` varchar(256) DEFAULT NULL COMMENT 'Name of node',
`Template` varchar(32) DEFAULT NULL COMMENT 'Template to display',
`Type` set('App','Game','Album','Track','Ebook') DEFAULT NULL COMMENT 'Type of products',
`NIDl` mediumint(8) unsigned NOT NULL COMMENT 'Reference to left Node-ID',
`NIDr` mediumint(8) unsigned NOT NULL COMMENT 'Reference to right Node-ID',
PRIMARY KEY (`NID`),
KEY `i_Flags` (`Flags`),
KEY `i_NIDl` (`NIDl`),
KEY `i_NIDr` (`NIDr`),
KEY `i_Type` (`Type`)
) ENGINE=MyISAM
mysql> EXPLAIN select * from t_tree t join t_tree p on t.nidl between p.nidl and p.nidr;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | t | ALL | i_NIDl | NULL | NULL | NULL | 1510 | |
| 1 | SIMPLE | p | ALL | i_NIDl,i_NIDr | NULL | NULL | NULL | 1510 | Range checked for each record (index map: 0xC) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
this query did not use index, how to solve this problem?