Much slower in joining two subqueries in FROM clause than SQL Server 2000

[Jeffery Huang]

MySQL seems not to use any indexes in place, nor to establish hash indexes, to join two subqueries in FROM clause within a single query. But SQL Server 2000 exceeds MySQL running the same query in joining the two subqueries using hash match. I am wondering if it's also possible in MySQL to optimize this query either using hash match or in other way. FYI, in MySQL the similar query with more than 10 paralleling subqueries takes like 10 seconds but only 2 seconds in the SQL Server 2000 in the very same PC server.

The following is the detail:

I have a large table, which has more than 10 million rows . Here goes the definition of this table:

CREATE TABLE "t_base_financeindex" (
"FID" int(11) NOT NULL default '0',
"FIsTradeData" bit(1) NOT NULL,
"FTrade" int(11) NOT NULL default '0',
"FStock" int(10) unsigned zerofill NOT NULL default '0000000000',
"FYear" char(4) character set utf8 NOT NULL default '',
"FQuarter" char(1) character set utf8 NOT NULL default '',
"FYearQuarter" char(6) NOT NULL,
"FTypeID" int(11) NOT NULL,
"FIndexValue" double default NULL,
PRIMARY KEY ("FStock","FTrade","FTypeID","FYear","FQuarter"),
KEY "FTypeID" ("FTypeID","FYearQuarter")
) ENGINE=InnoDB

and here goes the sql statement, which basically joines two subqueries in order to use one of their columns to obtain a new value for each FStock, on which two subqueries are joined:

SELECT P2_1.FSTOCK, (P2_1.fvalue - P2_2.fvalue) AS fvalue
FROM
(SELECT FStock, SUM(FIndexValue) AS fvalue
FROM t_base_financeIndex
WHERE t_base_financeIndex.FTypeID =98 AND
FYearQuarter = '2003-3'
GROUP BY FStock) AS P2_1
INNER JOIN
(SELECT FStock, SUM(FIndexValue) AS fvalue
FROM t_base_financeIndex
WHERE t_base_financeIndex.FTypeID = 98 AND
FYearQuarter = '2003-2'
GROUP BY FStock) AS P2_2
on P2_1.FStock = P2_2.FStock

1262 rows in set (0.54 sec)

Explain:
+----+-------------+---------------------+------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_
len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+---------+-----
----+------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL
| NULL | 1269 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL
| NULL | 1274 | Using where |
| 3 | DERIVED | t_base_financeIndex | ref | FTypeID | FTypeID | 16
| | 1874 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | t_base_financeIndex | ref | FTypeID | FTypeID | 16
| | 2124 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+---------------+---------+-----
----+------+------+----------------------------------------------+
4 rows in set (0.09 sec)

any idea on improving the process of joining two subqueries?