MySQL Forums :: General :: Why are no indexes used in this case?


Advanced Search

Why are no indexes used in this case?
Posted by: Steven De Groote ()
Date: February 03, 2013 06:38AM

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!

Options: ReplyQuote


Subject Written By Posted
Why are no indexes used in this case? Steven De Groote 02/03/2013 06:38AM
Re: Why are no indexes used in this case? Peter Brawley 02/03/2013 10:27AM
Re: Why are no indexes used in this case? Steven De Groote 02/03/2013 11:27AM
Re: Why are no indexes used in this case? Peter Brawley 02/03/2013 01:16PM
Re: Why are no indexes used in this case? Steven De Groote 02/03/2013 03:35PM
Re: Why are no indexes used in this case? Peter Brawley 02/03/2013 04:04PM
Re: Why are no indexes used in this case? Rick James 02/03/2013 03:59PM
Re: Why are no indexes used in this case? Steven De Groote 02/04/2013 03:01PM
Re: Why are no indexes used in this case? Rick James 02/04/2013 09:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.