Solon,
I'm not sure you can anything about getting rid of the Using temporary and Using filesort in your current schema. I do, however, think you would get significantly better performance if you weren't trying to group on a VARCHAR(100) field. Have you considered putting the page_type in a lookup table, and joining using a TINYINT field? It would cut the space used by the index, and subsequently, the time neededto process the index records in the group, considerably.
Consider a schema change of:
CREATE TABLE page_type (
page_type_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
, description VARCHAR(30) NOT NULL
, PRIMARY KEY pk_PageTypes (page_type_id)
) ENGINE=MyISAM;
INSERT INTO page_type (page_type_id, description)
VALUES (1,'None')
,(2,'Products Page')
,(3,'Touchpoint Portal')
,(4,'White Paper');
ALTER TABLE page_dim ADD COLUMN page_type_id TINYINT UNSIGNED NOT NULL;
LOCK TABLES page_dim WRITE:
UPDATE page_dim SET page_type_id=1 WHERE page_type='';
UPDATE page_dim SET page_type_id=2 WHERE page_type='Products Page';
UPDATE page_dim SET page_type_id=3 WHERE page_type='Touchpoint Portal';
UPDATE page_dim SET page_type_id=4 WHERE page_type='White Paper';
UNLOCK TABLES;
ALTER TABLE page_dim DROP INDEX ON (page_type);
ALTER TABLE page_dim DROP COLUMN page_type;
OPTIMIZE TABLE page_dim;
CREATE INDEX ix_page_type ON page_dim (page_type_id);
ANALYZE TABLE page_dim;
Then, re-run your group by expression.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com