Need help with under-performing query: using where, temporary and filesort

MySQL & MariaDB Other MySQL® Questions
1

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.

2

Well… your table structure is another table than the one you use in your query. )

3

You can get rid of the filesort by adding “ORDER BY NULL”.