CODEWITHSUNDEEP
mysqlperformanceoptimizationurl-rewriting
Aaron
Aaron

Reputation: 839

Slow MySQL Query "copying to tmp table", help re-writing it

This mysql query is extremely slow, when I look at the query it is "copying to tmp table". These tables are very large especially search_attribute (79 million rows) and search_attribute_values (350,000 rows)

SELECT attributenames.name, search_attribute_values.value 
FROM attributenames, categorysearchattributes, search_attribute, search_attribute_values 
WHERE 
categorysearchattributes.attributeid = attributenames.attributeid AND categorysearchattributes.categoryid = 4800 AND
categorysearchattributes.attributeid = search_attribute.attributeid AND 
search_attribute.valueid = search_attribute_values.valueid AND 
attributenames.localeid = 1 
GROUP BY search_attribute.valueid

Here is a picture of the EXPLAIN of the query

Here is my database schema

-- MySQL dump 10.13  Distrib 5.5.46, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.46-0ubuntu0.14.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



--
-- 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 `categorysearchattributes`
--

DROP TABLE IF EXISTS `categorysearchattributes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `categorysearchattributes` (
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `attributeid` bigint(20) NOT NULL DEFAULT '0',
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  KEY `caterysearchattributes_aID` (`attributeid`),
  KEY `caterysearchattributes_cID` (`categoryid`)
) 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`)
) 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 */;

Added attributeid_valueid index

Upvotes: 0

Views: 250

Answers (1)

Samir Selia
Samir Selia

Reputation: 7065

By looking at the EXPLAIN query in the picture, the table search_attribute is doing a full table scan i.e. not using indexes at all.

Adding index to valueid column of search_attribute table should make it faster. Give it a try and share your results after adding index.

ALTER TABLE `search_attribute` ADD KEY `idx_valueid` (`valueid`);

Please try the combinations of suggestions below.

Upvotes: 1

Related Questions