MySQL Forums
Forum List  »  Optimizer & Parser

Re: How do I avoid "Using file sort" and "Using temporary"
Posted by: Solon Barns
Date: March 06, 2006 08:27AM

Hi Jay,

Thanks for the response. Here's the additional info you asked:

CREATE TABLE `page_view_fact` (
`page_dim_id` int(11) NOT NULL default '0',
`registrant_dim_id` int(11) NOT NULL default '0',
`time_dim_id` int(11) NOT NULL default '0',
`time_on_page` int(11) NOT NULL default '0',
PRIMARY KEY (`page_dim_id`,`registrant_dim_id`,`time_dim_id`),
KEY `Refregistrant_dim16` (`registrant_dim_id`),
KEY `Reftime_dim18` (`time_dim_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `page_dim` (
`page_dim_id` int(11) NOT NULL auto_increment,
`page_type` varchar(100) NOT NULL default '',
`content_name` varchar(100) NOT NULL default '',
`page_name` varchar(100) NOT NULL default '',
`page_group` varchar(100) NOT NULL default '',
PRIMARY KEY (`page_dim_id`),
KEY `page_type` (`page_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `registrant_selection` (
`report_id` int(11) NOT NULL default '0',
`registrant_dim_id` int(11) NOT NULL default '0',
PRIMARY KEY (`report_id`,`registrant_dim_id`),
KEY `Refregistrant_dim29` (`registrant_dim_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> show keys from page_view_fact;
+----------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| page_view_fact | 0 | PRIMARY | 1 | page_dim_id | A | 510 | NULL | NULL | | BTREE | |
| page_view_fact | 0 | PRIMARY | 2 | registrant_dim_id | A | 100010 | NULL | NULL | | BTREE | |
| page_view_fact | 0 | PRIMARY | 3 | time_dim_id | A | 100010 | NULL | NULL | | BTREE | |
| page_view_fact | 1 | Refregistrant_dim16 | 1 | registrant_dim_id | A | 25002 | NULL | NULL | | BTREE | |
| page_view_fact | 1 | Reftime_dim18 | 1 | time_dim_id | A | 92 | NULL | NULL | | BTREE | |
+----------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.02 sec)

mysql> show keys from page_dim;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| page_dim | 0 | PRIMARY | 1 | page_dim_id | A | NULL | NULL | NULL | | BTREE | |
| page_dim | 1 | page_type | 1 | page_type | A | NULL | NULL | NULL | | BTREE | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

mysql> show keys from registrant_selection;
+----------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| registrant_selection | 0 | PRIMARY | 1 | report_id | A | NULL | NULL | NULL | | BTREE | |
| registrant_selection | 0 | PRIMARY | 2 | registrant_dim_id | A | 96921 | NULL | NULL | | BTREE | |
| registrant_selection | 1 | Refregistrant_dim29 | 1 | registrant_dim_id | A | NULL | NULL | NULL | | BTREE | |
+----------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

Thanks, Solon.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How do I avoid "Using file sort" and "Using temporary"
18372
March 06, 2006 08:27AM


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.