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

[Solon Barns]

I did:

mysql> ANALYZE TABLE page_dim;
+---------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------+---------+----------+----------+
| core_dimensional.page_dim | analyze | status | OK |
+---------------------------+---------+----------+----------+
1 row in set (0.22 sec)

mysql> SHOW INDEX 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 | 500 | NULL | NULL | | BTREE | |
| page_dim | 1 | page_type | 1 | page_type | A | 4 | NULL | NULL | | BTREE | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.03 sec)

However, the query is still slow and is still using "file sort" and "temporary". I wonder can it be because cardinality of page_type is only 4? (there are only four types of page in database currently).

If I replace the query with four separate queries, it looks more efficient way to do:

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 p
vf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87 where pd.page_type="Products Page";
+-------------------+
| sum(time_on_page) |
+-------------------+
| 104770 |
+-------------------+
1 row in set (0.28 sec)

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 p
vf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87 where pd.page_type="TouchPoint Portal";
+-------------------+
| sum(time_on_page) |
+-------------------+
| 116978 |
+-------------------+
1 row in set (0.28 sec)

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 p
vf.registrant_dim_id=rs.registrant_dim_id and rs.report_id=87 where pd.page_type="White Paper";
+-------------------+
| sum(time_on_page) |
+-------------------+
| 89591 |
+-------------------+
1 row in set (0.28 sec)

Thanks, Solon.