Why are no indexes used in this case?

[Steven De Groote]

Hi,

I'm struggling to find out why there is no index used on the "items" table.
Here's the query:

EXPLAIN SELECT *
FROM f1tgallery3.items
WHERE id
IN (

SELECT i.id
FROM f1tgallery3.items i
INNER JOIN f1tgallery3.items_tags it ON i.id = it.item_id
AND it.tag_id
IN (

SELECT id
FROM f1tgallery3.tags
WHERE name
IN (
'flowvis'
)
)
GROUP BY i.id
HAVING COUNT( DISTINCT it.tag_id ) =1
)
AND TYPE = 'photo'

The explain results show me the subquery is using indexes, but the primary query has possible_keys='type', but no keys are used.
The table definition is as below, so I would expect the primary key on column id to be used:

CREATE TABLE IF NOT EXISTS `items` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`description` text,
`name` varchar(255) DEFAULT NULL,
`parent_id` int(9) NOT NULL,
`title` varchar(255) DEFAULT NULL,
`type` varchar(32) NOT NULL,
`weight` int(9) NOT NULL DEFAULT '0',
`width` int(9) unsigned DEFAULT NULL,
`fullsize` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `type` (`type`),
KEY `weight` (`weight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2766 ;

Any ideas why the primary key is not used?
Right now, my query is slow (3s) with 5000 records in items, so not great at all :(

Thanks!