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 t
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,
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
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 h
