From: Peter Brawley
Date: October 19 2007 11:18pm
Subject: Re: Slow Subquery
List-Archive: http://lists.mysql.com/mysql/209586
Message-Id: <47193B34.4090903@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ryan,

 >Why is it so much faster?

Subquery optimisation in MySQL is a problem. For ideas see 'The 
unbearable slowness of IN()'  at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

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
>