Server uses all memory and CPU in remove_eq_conds

SELECT

        domains.domain_id,domains.domain_name,domain_checks.LAST

FROM

        projects2domains

JOIN

        domains ON ( projects2domains.domain_id = domains.domain_id )

JOIN

        projects ON (projects2domains.project_id = projects.id)

LEFT JOIN

        domain_checks ON (projects2domains.domain_id = domain_checks.domain_id AND domain_checks.searchengine_id = 50 )  

WHERE  projects.last_use > DATE_SUB( NOW( ) , INTERVAL 12 MONTH ) AND  ( domain_checks.LAST < DATE_SUB(CURDATE(),INTERVAL 30 DAY) OR ISNULL(domain_checks.LAST))  GROUP BY projects2domains.domain_id  ORDER BY domain_checks.LAST,domain_id DESC LIMIT 1;

-- MySQL dump 10.14  Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64)

--

-- Host: localhost    Database: crash

-- ------------------------------------------------------

-- Server version       5.5.30-MariaDB-mariadb1~squeeze-log

/*!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 */;

--

-- Current Database: `crash`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crash` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `crash`;

--

-- Table structure for table `domain_checks`

--

DROP TABLE IF EXISTS `domain_checks`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `domain_checks` (

  `domain_id` INT(11) UNSIGNED NOT NULL,

  `searchengine_id` INT(10) UNSIGNED NOT NULL,

  `last` DATE NOT NULL,

  UNIQUE KEY `domain_id` (`domain_id`,`searchengine_id`),

  KEY `searchengine_id` (`searchengine_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `domain_checks`

--

LOCK TABLES `domain_checks` WRITE;

/*!40000 ALTER TABLE `domain_checks` DISABLE KEYS */;

/*!40000 ALTER TABLE `domain_checks` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `domains`

--

DROP TABLE IF EXISTS `domains`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `domains` (

  `domain_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,

  `domain_name` VARCHAR(255) COLLATE utf8_bin NOT NULL,

  `domain_rname` VARCHAR(255) COLLATE utf8_bin NOT NULL,

  `ip` VARCHAR(15) COLLATE utf8_bin NOT NULL,

  `country_code` CHAR(2) COLLATE utf8_bin NOT NULL,

  `last_country` DATE NOT NULL,

  `last_crawler` DATE NOT NULL,

  `last_rank` DATE NOT NULL,

  PRIMARY KEY (`domain_id`),

  UNIQUE KEY `domain_name` (`domain_name`),

  KEY `domain_rname` (`domain_rname`),

  KEY `lastdownload` (`last_crawler`),

  KEY `last_country` (`last_country`),

  KEY `last_rank` (`last_rank`),

  KEY `ip` (`ip`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `domains`

--

LOCK TABLES `domains` WRITE;

/*!40000 ALTER TABLE `domains` DISABLE KEYS */;

/*!40000 ALTER TABLE `domains` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `projects`

--

DROP TABLE IF EXISTS `projects`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `projects` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `customer` INT(10) UNSIGNED NOT NULL,

  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,

  `last_use` datetime NOT NULL,

  `googleAPIaccountEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,

  `googleAPIaccountPassword` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `googleAPIclientEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,

  `googleAPIdeveloperToken` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `googleAPIapplicationToken` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `googleAdwordsAllowChanges` tinyint(4) NOT NULL,

  `googleAdwordsCampaign` VARCHAR(64) COLLATE utf8_bin NOT NULL,

  `googleAnalyticsEmail` VARCHAR(64) COLLATE utf8_bin NOT NULL,

  `googleAnalyticsPassword` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `googleAnalyticsAccount` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `googleAnalyticsGoal` VARCHAR(32) COLLATE utf8_bin NOT NULL,

  `piwikURL` VARCHAR(255) COLLATE utf8_bin NOT NULL,

  `piwikAUTH` CHAR(32) COLLATE utf8_bin NOT NULL,

  `piwikSITE` INT(10) UNSIGNED NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`),

  UNIQUE KEY `reseller_id` (`customer`,`id`),

  KEY `reseller` (`customer`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `projects`

--

LOCK TABLES `projects` WRITE;

/*!40000 ALTER TABLE `projects` DISABLE KEYS */;

/*!40000 ALTER TABLE `projects` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Table structure for table `projects2domains`

--

DROP TABLE IF EXISTS `projects2domains`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `projects2domains` (

  `project_id` INT(10) UNSIGNED NOT NULL,

  `domain_id` INT(11) UNSIGNED NOT NULL,

  `competitor` INT(1) NOT NULL,

  PRIMARY KEY (`project_id`,`domain_id`),

  KEY `domain_id` (`domain_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=FIXED;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `projects2domains`

--

LOCK TABLES `projects2domains` WRITE;

/*!40000 ALTER TABLE `projects2domains` DISABLE KEYS */;

/*!40000 ALTER TABLE `projects2domains` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

#0  0x0000000000808dc1 in Arg_comparator::is_owner_equal_func (this=0x7fd9fb4aff98) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:107

#1  0x00000000007f7391 in Arg_comparator::set_compare_func (this=0x7fd9fb4aff98, item=0x7fd9fb4afed0, type=TIME_RESULT) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:591

#2  0x00000000007f80b5 in Arg_comparator::set_cmp_func (this=0x7fd9fb4aff98, owner_arg=0x7fd9fb4afed0, a1=0x7fd9fb4aff60, a2=0x7fd9fb4aff68, type=TIME_RESULT) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:796

#3  0x000000000067a9f2 in Arg_comparator::set_cmp_func (this=0x7fd9fb4aff98, owner_arg=0x7fd9fb4afed0, a1=0x7fd9fb4aff60, a2=0x7fd9fb4aff68, set_null_arg=true) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:76

#4  0x000000000067aa39 in Item_bool_func2::set_cmp_func (this=0x7fd9fb4afed0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.h:376

#5  0x00000000007f7335 in Item_bool_func2::fix_length_and_dec (this=0x7fd9fb4afed0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:583

#6  0x0000000000820f19 in Item_func::fix_fields (this=0x7fd9fb4afed0, thd=0x31f1a00, ref=0x7fd9fb4b0140) at /home/elenst/bzr/5.5/sql/item_func.cc:231

#7  0x00000000008038e4 in Item_cond::fix_fields (this=0x7fd9fb4b0048, thd=0x31f1a00, ref=0x7fdb17a27fa0) at /home/elenst/bzr/5.5/sql/item_cmpfunc.cc:4299

#8  0x000000000065f793 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fd9fb4b0048, cond_value=0x7fdb17a28188) at /home/elenst/bzr/5.5/sql/sql_select.cc:13597

#9  0x000000000065f053 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fdad001ab28, cond_value=0x7fdb17a28298) at /home/elenst/bzr/5.5/sql/sql_select.cc:13453

#10 0x000000000065f053 in internal_remove_eq_conds (thd=0x31f1a00, cond=0x7fdad004a360, cond_value=0x7fdad001b578) at /home/elenst/bzr/5.5/sql/sql_select.cc:13453

#11 0x000000000065fbce in remove_eq_conds (thd=0x31f1a00, cond=0x7fdad004a360, cond_value=0x7fdad001b578) at /home/elenst/bzr/5.5/sql/sql_select.cc:13700

#12 0x000000000065ef34 in optimize_cond (join=0x7fdad001b258, conds=0x7fdad004a360, join_list=0x31f4920, ignore_on_conds=false, cond_value=0x7fdad001b578, cond_equal=0x7fdad001b698) at /home/elenst/bzr/5.5/sql/sql_select.cc:13412

#13 0x000000000063f81a in JOIN::optimize (this=0x7fdad001b258) at /home/elenst/bzr/5.5/sql/sql_select.cc:1070

#14 0x00000000006469e1 in mysql_select (thd=0x31f1a00, rref_pointer_array=0x31f4a08, tables=0x7fdad0006e28, wild_num=0, fields=..., conds=0x7fdad001ac38, og_num=3, order=0x7fdad001aff0, group=0x7fdad001ae98, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fdad001b238, unit=0x31f40d8, select_lex=0x31f47b0) at /home/elenst/bzr/5.5/sql/sql_select.cc:3048

#15 0x000000000063d5ea in handle_select (thd=0x31f1a00, lex=0x31f4028, result=0x7fdad001b238, setup_tables_done_option=0) at /home/elenst/bzr/5.5/sql/sql_select.cc:318

#16 0x000000000061643d in execute_sqlcom_select (thd=0x31f1a00, all_tables=0x7fdad0006e28) at /home/elenst/bzr/5.5/sql/sql_parse.cc:4641

#17 0x000000000060ee8c in mysql_execute_command (thd=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_parse.cc:2195

#18 0x0000000000618cdc in mysql_parse (thd=0x31f1a00, rawbuf=0x7fdad00064f8 "SELECT\n        domains.domain_id,domains.domain_name,domain_checks.LAST\nFROM\n        projects2domains\nJOIN\n        domains ON ( projects2domains.domain_id = domains.domain_id )\nJOIN\n        projects O"..., length=627, parser_state=0x7fdb17a29550) at /home/elenst/bzr/5.5/sql/sql_parse.cc:5759

#19 0x000000000060c3dc in dispatch_command (command=COM_QUERY, thd=0x31f1a00, packet=0x32e1021 "SELECT\n        domains.domain_id,domains.domain_name,domain_checks.LAST\nFROM\n        projects2domains\nJOIN\n        domains ON ( projects2domains.domain_id = domains.domain_id )\nJOIN\n        projects O"..., packet_length=627) at /home/elenst/bzr/5.5/sql/sql_parse.cc:1068

#20 0x000000000060b61d in do_command (thd=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_parse.cc:794

#21 0x00000000007109c1 in do_handle_one_connection (thd_arg=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1266

#22 0x00000000007103a8 in handle_one_connection (arg=0x31f1a00) at /home/elenst/bzr/5.5/sql/sql_connect.cc:1181

#23 0x00007fdb17035e9a in start_thread (arg=0x7fdb17a2a700) at pthread_create.c:308

#24 0x00007fdb16329cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

revision-id: wlad@montyprogram.com-20130429222947-so2nbrsb1t03hnp6

revno: 3739

branch-nick: 5.5