Slow mysql query, copying to tmp table, using filesort

Question

I need to speed up this query. It takes 12 seconds, returns 3917 records. My installation of mySQL is not tuned for performance, maybe I need to modify some configuration variables to help performance. The query says "Copying to tmp table" for most of the duration of the query.

This query is used to generate the filters for parametric searching (ie. filtering search results by brand name, color, etc).

Query:

SELECT  attributenames.attributeid,
        search_attribute_values.valueid,
        attributenames.name,
        search_attribute_values.value,
        count(search_attribute_values.value) as count,
        search_attribute_values.absolutevalue
    FROM  product
    INNER JOIN  vendorimport
               ON (vendorimport.productid = product.productid
              AND  product.categoryid = 4871)
    INNER JOIN  search_attribute
               ON (search_attribute.productid = product.productid
              AND  search_attribute.localeid = 1)
    INNER JOIN  search_attribute_values
               ON (search_attribute.valueid = search_attribute_values.valueid)
    INNER JOIN  attributenames
               ON (attributenames.attributeid = search_attribute.attributeid
              AND  attributenames.localeid = 1)
    GROUP BY  attributenames.attributeid, search_attribute_values.valueid

Explain:

+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+
| id | select_type | table                   | type   | possible_keys                                      | key                        | key_len | ref                                   | rows  | Extra                                        |
+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | product                 | ref    | PRIMARY,product_categoryID,categoryid_productid    | categoryid_productid       | 4       | const                                 | 38729 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | vendorimport            | ref    | productimport_productid                            | productimport_productid    | 5       | microcad.product.productid            |     1 | Using where; Using index                     |
|  1 | SIMPLE      | search_attribute        | ref    | PRIMARY                                            | PRIMARY                    | 8       | microcad.vendorimport.productid,const |     8 | Using where; Using index                     |
|  1 | SIMPLE      | attributenames          | ref    | attributenames_attributeID,attributenames_localeID | attributenames_attributeID | 8       | microcad.search_attribute.attributeid |     4 | Using where                                  |
|  1 | SIMPLE      | search_attribute_values | eq_ref | PRIMARY                                            | PRIMARY                    | 4       | microcad.search_attribute.valueid     |     1 |                                              |
+----+-------------+-------------------------+--------+----------------------------------------------------+----------------------------+---------+---------------------------------------+-------+----------------------------------------------+

Schema:

--
-- Table structure for table `attributenames`
--

DROP TABLE IF EXISTS `attributenames`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributenames` (
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `name` varchar(110) NOT NULL DEFAULT '',
  `localeid` int(11) NOT NULL DEFAULT '0',
  KEY `attributenames_attributeID` (`attributeid`),
  KEY `attributenames_localeID` (`localeid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product` (
  `productid` int(11) NOT NULL DEFAULT '0',
  `manufacturerid` int(11) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  `mfgpartno` varchar(70) NOT NULL DEFAULT '',
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `isaccessory` tinyint(1) NOT NULL DEFAULT '0',
  `equivalency` double NOT NULL DEFAULT '0',
  `creationdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastupdated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`productid`),
  KEY `product_manufacturerID` (`manufacturerid`),
  KEY `product_categoryID` (`categoryid`),
  KEY `product_mfgPartNo` (`mfgpartno`),
  KEY `categoryid_productid` (`categoryid_productid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `search_attribute`
--

DROP TABLE IF EXISTS `search_attribute`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `search_attribute` (
  `productid` int(11) NOT NULL DEFAULT '0',
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `valueid` int(11) NOT NULL DEFAULT '0',
  `localeid` int(11) NOT NULL DEFAULT '0',
  `setnumber` tinyint(2) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productid`,`localeid`,`attributeid`,`setnumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `search_attribute_values`
--

DROP TABLE IF EXISTS `search_attribute_values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `search_attribute_values` (
  `valueid` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  `absolutevalue` double NOT NULL DEFAULT '0',
  `unitid` int(11) NOT NULL DEFAULT '0',
  `isabsolute` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`valueid`),
  KEY `search_attrval_value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Number of Records in each table:

search_attribute is 72,000,000, search_attribute_values is 350,000, product is 4,000,000

Answer 1

NULL vs NOT NULL -- Use NOT NULL unless you have a business reason for NULL.

Use InnoDB with relevant PRIMARY KEY on each table. That will probably be faster.

"Using index", where it makes sense, will help some.

product.categoryid = 4871 does not belong in the ON clause for vendorimport; move it to a WHERE clause. (This won't speed things up.)

Your query cannot be optimizer further -- it must do all the JOINs and deliver all the rows.

But... Do you really want 3917 rows of output? Can you deal with that? Maybe you only want a few of them and could filter them during the SELECT? That could speed things up.