List:General Discussion« Previous MessageNext Message »
From:Ryan Bates Date:October 19 2007 10:57pm
Subject:Slow Subquery
View as plain text  
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
Thread
Slow SubqueryRyan Bates20 Oct
  • Re: Slow SubqueryPeter Brawley20 Oct
  • Re: Slow SubqueryBaron Schwartz20 Oct
  • Re: Slow SubqueryBrent Baisley23 Oct
    • Re: Slow SubqueryJay Pipes23 Oct