How do I avoid "Using file sort" and "Using temporary"
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"?
Subject
Views
Written By
Posted
How do I avoid "Using file sort" and "Using temporary"
32995
March 03, 2006 03:20PM
19900
March 04, 2006 11:46AM
18462
March 06, 2006 08:27AM
18301
March 06, 2006 10:16AM
16553
March 06, 2006 12:51PM
18071
March 07, 2006 11:42AM
19801
March 10, 2006 03:01PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.