MySQL Forums
Forum List  »  Optimizer & Parser

How do I avoid "Using file sort" and "Using temporary"
Posted by: Solon Barns
Date: March 03, 2006 03:20PM

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"?

Options: ReplyQuote


Subject
Views
Written By
Posted
How do I avoid "Using file sort" and "Using temporary"
31283
March 03, 2006 03:20PM


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.