MySQL Forums
Forum List  »  Optimizer & Parser

Re: How do I avoid "Using file sort" and "Using temporary"
Posted by: Jay Pipes
Date: March 07, 2006 11:42AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How do I avoid "Using file sort" and "Using temporary"
18010
March 07, 2006 11:42AM


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.