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

[Solon Barns]

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.