Skip navigation links

MySQL Forums :: Optimizer & Parser :: How do I avoid "Using file sort" and "Using temporary"


Advanced Search

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" 29358 Solon Barns 03/03/2006 03:20PM
Re: How do I avoid "Using file sort" and "Using temporary" 18649 Jay Pipes 03/04/2006 11:46AM
Re: How do I avoid "Using file sort" and "Using temporary" 17332 Solon Barns 03/06/2006 08:27AM
Re: How do I avoid "Using file sort" and "Using temporary" 17238 Jay Pipes 03/06/2006 10:16AM
Re: How do I avoid "Using file sort" and "Using temporary" 15393 Solon Barns 03/06/2006 12:51PM
Re: How do I avoid "Using file sort" and "Using temporary" 16859 Jay Pipes 03/07/2006 11:42AM
Re: How do I avoid "Using file sort" and "Using temporary" 18500 Solon Barns 03/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.