Indeed, as you say, Brent, correlated subqueries are not well-optimized
in MySQL. The specific subquery (the IN() subquery) demonstrated in the
original post is, however, optimized in MySQL 6.0 :)
More comments inline.
Brent Baisley wrote:
> You are using a correlated subquery, which MySQL is terrible at.
> Whenever you find yourself doing a correlated subquery, see if you can
> switch it to a derived table with a join, which MySQL is far better at.
> A derived table is like a "virtual" table you create on the fly. It's
> very simple, just assign a name to your query and then treat it as if it
> is a regular table.
Actually, in this case, no need for a derived table. A simple join will
suffice:
SELECT * FROM projects p
JOIN project_tags pt ON p.project_id = pt.project_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE tags.name='foo';
Make sure you've got indexes on p (project_id), pt (project_id, tag_id),
t (name)
Cheers,
Jay
> So your query would look something like this:
> SELECT projects.* FROM projects
> JOIN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE
> tags.name='foo' AND projects_tags.project_id=projects.id) AS ptagids
> ON project.id=ptagids.project_id
>
> Your IN has become a JOIN and mysql optimizes it far better.
>
> On Oct 19, 2007, at 6:57 PM, Ryan Bates wrote:
>
>> 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/mysql?unsub=1
>>
>
>
