Index not utilized when using IN (subquery)

[Hans Meiser]

Hi,

I'm running the following query and it does a full table scan in the material table, despite id being my primary index.
any suggestions ?

explain select name from material where id in
(select material_id from material_acls where type='user' and acl_id = 1
UNION
select material_id from material_acls where type='group' and acl_id in
(select group_id from users_groups_members where member_id=1)
UNION
select material_id from material_acls where type='special' and acl_id IN (1,2)
);

+----+--------------------+----------------------+------+-------------------------------------+-----------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+------+-------------------------------------+-----------------+---------+-------------+------+--------------------------+
| 1 | PRIMARY | material | ALL | NULL | NULL | NULL | NULL | 47 | Using where |
| 2 | DEPENDENT SUBQUERY | material_acls | ref | type_acl_id_idx,type_idx,acl_id_idx | type_acl_id_idx | 5 | const,const | 1 | Using where; Using index |
| 3 | DEPENDENT UNION | material_acls | ref | type_acl_id_idx,type_idx | type_idx | 1 | const | 2 | Using where; Using index |
| 4 | DEPENDENT SUBQUERY | users_groups_members | ref | member_id_idx | member_id_idx | 4 | const | 1 | Using where |
| 5 | DEPENDENT UNION | material_acls | ref | type_acl_id_idx,type_idx,acl_id_idx | type_acl_id_idx | 1 | const | 3 | Using where; Using index |
|NULL | UNION RESULT | <union2,3,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+----------------------+------+-------------------------------------+-----------------+---------+-------------+------+----------------

regards

Hans