How do I avoid "Using file sort" and "Using temporary"

[Solon Barns]

I am running MySQL 4.1.14-nt and using MyISAM tables.

I have a star-schema layout with 2 dimensions:


mysql> describe registrant_dim;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| registrant_dim_id | int(11) | | PRI | NULL | auto_increment |
| registrant_id | int(11) | | | 0 | |
| name | varchar(100) | | | | |
| email | varchar(100) | | | | |
| source | varchar(100) | | | | |
| company | varchar(100) | | | | |
+---------------------+--------------+------+-----+---------+----------------+
20 rows in set (0.06 sec)


mysql> describe page_dim;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| page_dim_id | int(11) | | PRI | NULL | auto_increment |
| page_type | varchar(100) | | MUL | | |
| page_name | varchar(100) | | | | |
| page_group | varchar(100) | | | | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

I also have a fact table:

mysql> describe page_view_fact;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| page_dim_id | int(11) | | PRI | 0 | |
| registrant_dim_id | int(11) | | PRI | 0 | |
| time_dim_id | int(11) | | PRI | 0 | |
| time_on_page | int(11) | | | 0 | |
+-------------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

And a registrant selection table:

mysql> describe registrant_selection;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| report_id | int(11) | | PRI | 0 | |
| registrant_dim_id | int(11) | | PRI | 0 | |
+-------------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

If I want to get the total time_on_page for selected registrants, the query is fast:

mysql> select sum(time_on_page) from page_view_fact pvf join page_dim pd on pvf.page_dim_id=pd.page_dim_id join registrant_selection rs on pvf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87;
+-------------------+
| sum(time_on_page) |
+-------------------+
| 412503 |
+-------------------+
1 row in set (0.17 sec)

Now if I want to get the time_on_page for selected registrants by page type, the query is slow:

mysql> select pd.page_type,sum(time_on_page) from page_view_fact pvf join page_dim pd on pvf.page_dim_id=pd.page_dim_id join registrant_selection rs on pvf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87 group by pd.page_type;
+-------------------+-------------------+
| page_type | sum(time_on_page) |
+-------------------+-------------------+
| | 101164 |
| Products Page | 104770 |
| TouchPoint Portal | 116978 |
| White Paper | 89591 |
+-------------------+-------------------+
4 rows in set (4.32 sec)

Explain shows "Using temporary; Using filesort":

mysql> explain select pd.page_type,sum(time_on_page) from page_view_fact pvf join page_dim pd on pvf.page_dim_id=pd.page_dim_id join registr
ant_selection rs on pvf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87 group by pd.page_type;
+----+-------------+-------+--------+-----------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+--------+-----------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | rs | ref | PRIMARY,Refregistrant_dim29 | PRIMARY | 4 | const |1046 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | pvf | ref | PRIMARY,Refregistrant_dim16 | Refregistrant_dim16 | 4 | core_dimensional.rs.registrant_dim_id | 4 | |
| 1 | SIMPLE | pd | eq_ref | PRIMARY | PRIMARY | 4 | core_dimensional.pvf.page_dim_id | 1 | |
+----+-------------+-------+--------+-----------------------------+---------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

The only difference in explain from previous query is "Using temporary; Using filesort". So I think this is the reason why query is slow.

Is there a way that I can write my query and avoid "file sort" and "temporary"?