Hey, guys.

Having the following question:
I have a nested table in mysql(>=3.23 compatibility needed) with the
following structure:
+----------------+-----------------------+------+-----+---------
+-------+
| Field | Type | Null | Key | Default |
Extra |
+----------------+-----------------------+------+-----+---------
+-------+
| id | mediumint(5) unsigned | NO | |
| |
| ns_id | mediumint(5) unsigned | NO | PRI |
| |
| ns_level | smallint(5) unsigned | NO | MUL |
| |
| parent_id | mediumint(5) unsigned | NO | MUL |
| |
| ns_left_index | mediumint(6) unsigned | NO | PRI |
| |
| ns_right_index | mediumint(6) unsigned | NO | PRI |
| |
| ns_order | smallint(5) unsigned | NO | |
| |
| ns_ignore | enum('0','1') | NO | |
| |
| type_id | smallint(5) unsigned | NO | |
| |
| name_id | mediumint(6) unsigned | NO | MUL |
| |
| acl_id | mediumint(5) unsigned | NO | |
| |
+----------------+-----------------------+------+-----+---------
+-------+

Its a basic nested table plus some modifications, so that the nodes in
it can be duplicated:
`ns_id` is an unique id
and `id` becomes the `ns_id` of a copied node
well, that's not so important.

I need to run a query to load the parent-trees for a set of nodes:
SELECT DISTINCT
`parent`.`id`,`parent`.`ns_id`,`parent`.`parent_id `,`parent`.`ns_level`,`parent`.`ns_left_index`,`pa rent`.`ns_right_index`,`parent`.`ns_order`,`parent `.`ns_ignore`,`parent`.`type_id`,`s1`.`value`
as `name`,`parent`.`name_id`,`parent`.`acl_id` FROM `ntable` AS
node,`ntable` AS parent LEFT JOIN `table2` `s1` ON
(`parent`.`name_id`=`s1`.`id`) WHERE `node`.`ns_left_index` BETWEEN
`parent`.`ns_left_index` AND `parent`.`ns_right_index` AND `node`.`id`
IN
(19013,19640,19257,19647,19354,19651,19196,19654,1 9258,19260,19262,19264,19355,19357,19359,19361,193 63)
&& ((`s1`.`lang`='1') || `s1`.`lang` IS NULL) ORDER BY
`parent`.`ns_left_index`

Note that we have `node`.`id` IN ... that matches a set of
nodes(copies).
This is a standard "nested-way" of loading a path to a node, but using
a set of nodes in this case.

There is a join to another table table2, here's its structure:
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | mediumint(6) | NO | MUL | | |
| value | text | NO | | | |
| lang | enum('0','1') | NO | MUL | | |
+-------+---------------+------+-----+---------+-------+

And the problem.....
It takes 1.26 seconds to load and I can't afford that, because there
will be more than one such query executed per session.

What I've notices is that it doesn't use indexes actually, here's
EXPLAIN SELECT...
+----+-------------+--------+------+-----------------------+------
+---------+----------------------------+-------
+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra |
+----+-------------+--------+------+-----------------------+------
+---------+----------------------------+-------
+-----------------------------------------------------------+
| 1 | SIMPLE | parent | ALL | ns_both,ns_left_index | NULL |
NULL | NULL | 21864 | Using temporary; Using
filesort |
| 1 | SIMPLE | s1 | ref | id | id |
3 | db.parent.name_id | 1 | Using
where |
| 1 | SIMPLE | node | ALL | ns_both,ns_left_index | NULL |
NULL | NULL | 21864 | Range checked for each
record (index map: 0x22); Distinct |
+----+-------------+--------+------+-----------------------+------
+---------+----------------------------+-------
+-----------------------------------------------------------+


And FORCE|USE KEY directives are just ignored...


Pretty complicated, yeah...

So, can you advise something?