I'm trying to determine why a subquery is slower than running two
separate queries. I have a simple many-to-many association using 3
tables: projects, tags and projects_tags. Here's the query I'm using
to find the projects with a given tag:
SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id
FROM tags, projects_tags WHERE tags.name='foo' AND
projects_tags.project_id=projects.id);
(0.36 sec)
Compare that with splitting it into two queries:
SELECT projects_tags.project_id FROM tags, projects_tags WHERE
tags.name='foo' AND projects_tags.project_id=projects.id
(0.00 sec) /* returns 1, 2, 3 */
SELECT * FROM projects WHERE id IN (1, 2, 3);
(0.00 sec)
Why is it so much faster? Looking at the explain statement (below) of
the one with the subquery, it appears it's not using the primary key
index on the projects table. Why is it that MySQL doesn't perform
this simple optimization? And is there a solution that will allow me
to still use a subquery?
I realize I can use a join instead of a subquery, but this is a
simplified example.
Here's the explain statement:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: projects
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 15433
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: tags
type: ref
possible_keys: PRIMARY,index_tags_on_name
key: index_tags_on_name
key_len: 258
ref: const
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: projects_tags
type: ref
possible_keys: tag_id
key: tag_id
key_len: 5
ref: my_database.tags.id
rows: 10
Extra: Using where
Here's the table dumps:
CREATE TABLE `projects` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `tags` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `index_tags_on_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `projects_tags` (
`project_id` int(11) default NULL,
`tag_id` int(11) default NULL,
KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I'm using MySQL 5.0.37. Thanks in advance.
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
