Re: slow query in Inndb

[Sylwester Zdanowski]

Thanks, I will try but ther's a catch.
All those mentioned things are basic elements for this database. I've seen another instalation of this database with more data working faster.

>Am I blind, or is there no table "e" in your query?
I have not noticed, but run query again with the same resault. NO table "e" and yet "e" in explain table column.

I made mysqldump to get Create:

CREATE TABLE `assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tariffid` int(11) NOT NULL DEFAULT '0',
  `liabilityid` int(11) NOT NULL DEFAULT '0',
  `customerid` int(11) NOT NULL DEFAULT '0',
  `period` smallint(6) NOT NULL DEFAULT '0',
  `at` int(11) NOT NULL DEFAULT '0',
  `datefrom` int(11) NOT NULL DEFAULT '0',
  `dateto` int(11) NOT NULL DEFAULT '0',
  `invoice` tinyint(1) NOT NULL DEFAULT '0',
  `suspended` tinyint(1) NOT NULL DEFAULT '0',
  `settlement` tinyint(1) NOT NULL DEFAULT '0',
  `discount` decimal(6,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tariffid` (`tariffid`),
  KEY `customerid` (`customerid`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

CREATE TABLE `cash` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `userid` int(11) NOT NULL DEFAULT '0',
  `value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `taxid` int(11) NOT NULL DEFAULT '0',
  `customerid` int(11) NOT NULL DEFAULT '0',
  `comment` text COLLATE utf8_polish_ci NOT NULL,
  `docid` int(11) NOT NULL DEFAULT '0',
  `itemid` smallint(6) NOT NULL DEFAULT '0',
  `importid` int(11) DEFAULT NULL,
  `sourceid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customerid` (`customerid`),
  KEY `docid` (`docid`),
  KEY `time` (`time`),
  KEY `importid` (`importid`),
  KEY `sourceid` (`sourceid`)
) ENGINE=InnoDB AUTO_INCREMENT=597 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

/*!50001 DROP VIEW IF EXISTS `customersview`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `customersview` (
  `id` int(11),
  `lastname` varchar(128),
  `name` varchar(128),
  `status` smallint(6),
  `email` varchar(255),
  `address` varchar(255),
  `zip` varchar(10),
  `city` varchar(32),
  `ten` varchar(16),
  `ssn` varchar(11),
  `regon` varchar(255),
  `rbe` varchar(255),
  `icn` varchar(255),
  `info` text,
  `notes` text,
  `serviceaddr` text,
  `creationdate` int(11),
  `moddate` int(11),
  `creatorid` int(11),
  `modid` int(11),
  `deleted` tinyint(1),
  `message` text,
  `pin` int(6),
  `cutoffstop` int(11),
  `consentdate` int(11),
  `type` smallint(6),
  `divisionid` int(11),
  `paytime` tinyint(4),
  `countryid` int(11),
  `paytype` varchar(255),
  `mailingnotice` tinyint(1),
  `invoicenotice` tinyint(1),
  `einvoice` tinyint(1)
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;