MySQL Forums
Forum List  »  Performance

Slow mysql query, copying to tmp table, using filesort
Posted by: Aaron Bains
Date: December 07, 2015 04:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow mysql query, copying to tmp table, using filesort
3709
December 07, 2015 04:50AM


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.