JOIN performance and subqueries

Suppose I have a table called items, and I have queries Q1, Q2, and Q3
that reduce the number of records I am going to look at by a
significant amount. Each of these queries just returns item_id--their
sole purpose is to filter the records I look at in the items table.
In this example, I use the trivial queries:
SELECT item_id FROM items WHERE item_id < 1000;
SELECT item_id FROM items WHERE item_id < 2000;
SELECT item_id FROM items WHERE item_id < 3000;
but you can imagine each Qn is something more complicated than this.

I have played with 2 different ways of applying Q1, Q2, and Q3 to try
to figure out which is better.

First, I just ran Q1, Q2, and Q3 as sub queries and JOINed them as
temporary tables to items.

Next, I split the WHERE clauses off of Q1, Q2, and Q3 and added them
to the JOIN clauses so I had something that did not require any sub
queries.

With the queries I include here, the second method is much faster,
and the explain output looks much better. However, when I used some
more complicated queries for Q1, Q2, and Q3 that involved JOINs on
other tables, the first method was faster, even though the explain
output was uglier. My question is this:




Was that an anomaly or a result of me only having a few thousand
records in my test table, or are there some cases where it is better
to JOIN on temporary tables even if it can be avoided?




Here are the queries and explain outputs:
<pre>
SELECT items.item_id
FROM items
JOIN (SELECT item_id FROM items WHERE item_id < 1000) AS temp1 ON
items.item_id = temp1.item_id
JOIN (SELECT item_id FROM items WHERE item_id < 2000) AS temp2 ON
items.item_id = temp2.item_id
JOIN (SELECT item_id FROM items WHERE item_id < 3000) AS temp3 ON
items.item_id = temp3.item_id
GROUP BY items.item_id;

mysql> explain SELECT items.item_id FROM items JOIN (SELECT item_id
FROM items WHERE item_id < 1000) AS temp1 ON items.item_id =
temp1.item_id JOIN (SELECT item_id FROM items WHERE item_id < 2000) AS
temp2 ON items.item_id = temp2.item_id JOIN (SELECT item_id FROM items
WHERE item_id < 3000) AS temp3 ON items.item_id = temp3.item_id GROUP
BY items.item_id;
+----+-------------+------------+--------+---------------+---------
+---------+---------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------
+---------+---------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL |
NULL | NULL | 817 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL |
NULL | NULL | 860 | Using where |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL |
NULL | NULL | 863 | Using where |
| 1 | PRIMARY | items | eq_ref | PRIMARY | PRIMARY |
4 | temp3.item_id | 1 | Using where; Using index |
| 4 | DERIVED | items | range | PRIMARY | PRIMARY |
4 | NULL | 926 | Using where; Using index |
| 3 | DERIVED | items | range | PRIMARY | PRIMARY |
4 | NULL | 922 | Using where; Using index |
| 2 | DERIVED | items | range | PRIMARY | PRIMARY |
4 | NULL | 873 | Using where; Using index |
+----+-------------+------------+--------+---------------+---------
+---------+---------------+------+---------------------------------+


select items.item_id
FROM items
JOIN items AS i1 ON items.item_id = i1.item_id AND i1.item_id < 1000
JOIN items AS i2 ON items.item_id = i2.item_id AND i2.item_id < 2000
JOIN items AS i3 ON items.item_id = i3.item_id AND i3.item_id < 3000
GROUP BY items.item_id;

mysql> explain select items.item_id FROM items JOIN items AS i1 ON
items.item_id = i1.item_id AND i1.item_id < 1000 JOIN items AS i2 ON
items.item_id = i2.item_id AND i2.item_id < 2000 JOIN items AS i3 ON
items.item_id = i3.item_id AND i3.item_id < 3000 GROUP BY
items.item_id;
+----+-------------+-------+--------+---------------+---------
+---------+--------------------------+------+--------------------------
+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------
+---------+--------------------------+------+--------------------------
+
| 1 | SIMPLE | items | range | PRIMARY | PRIMARY |
4 | NULL | 873 | Using where; Using index
|
| 1 | SIMPLE | i1 | eq_ref | PRIMARY | PRIMARY |
4 | item_stuff.items.item_id | 1 | Using index
|
| 1 | SIMPLE | i2 | eq_ref | PRIMARY | PRIMARY |
4 | item_stuff.i1.item_id | 1 | Using where; Using index
|
| 1 | SIMPLE | i3 | eq_ref | PRIMARY | PRIMARY |
4 | item_stuff.i1.item_id | 1 | Using where; Using index
|
+----+-------------+-------+--------+---------------+---------
+---------+--------------------------+------+--------------------------
+

</pre>