List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:October 19 2007 11:37pm
Subject:Re: Slow Subquery
View as plain text  
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)

As another poster said, this kind of subquery runs slowly, but just to 
elaborate on it: it's not every subquery that's a problem, just IN() and 
NOT IN().  Use EXPLAIN EXTENDED followed by SHOW WARNINGS to see what's 
happening.

Baron
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