MySQL Performance: MySQL => Need help with under-performing query: using where, temporary and filesort

Members

Help

Home

Home » Performance » MySQL » Need help with under-performing query: using where, temporary and filesort

Show: Today's Messages :: Show Polls :: Message Navigator

Need help with under-performing query: using where, temporary and filesort [message #3714]

Thu, 13 November 2008 05:23

sameerpanjwani
Messages: 5
Registered: August 2008

Junior Member

This is the table:

CREATE TABLE common_categories (
id int(11) NOT NULL auto_increment,
category varchar(255) NOT NULL default '',
category_parent_id int(11) NOT NULL default '0',
category_count varchar(255) NOT NULL default '',
parent_category varchar(255) NOT NULL default '',
visibility enum('Visible','Invisible') NOT NULL default 'Visible',
related_words varchar(255) NOT NULL default '',
did_list text,
PRIMARY KEY (id),
UNIQUE KEY category (category,category_parent_id),
KEY category_2 (category),
KEY parent_category (parent_category),
KEY category_parent_id (category_parent_id),
FULLTEXT KEY category_3 (category),
FULLTEXT KEY related_words (related_words)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And this is the query:
SELECT title, count(title) as number FROM did_category dc WHERE dc.main_category LIKE 'Business%' GROUP BY dc.title

The table has more than 1 million records and the EXPLAIN shows it using the main_category as the key but it also shows using where, temporary and filesort. I would assume that is slowing down the query. How to avoid that.

Report message to a moderator