GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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. )

    Comment


    • #3
      You can get rid of the filesort by adding "ORDER BY NULL".

      Comment

      Working...
      X