0

The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it?

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   (SELECT consoleId,
               servertime,
               nocName,
               eventIdx,
               nocStatus,
               nid,
               site,
               machine,
               clientversion,
               timeExecuted
        FROM   temptest
        ORDER  BY tid DESC) AS x
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;

The table definition is

CREATE TABLE `temptest`
  (
     `tid`                INT(10) NOT NULL AUTO_INCREMENT,
     `eventIdx`           INT(11) NOT NULL DEFAULT '0',
     `servertime`         INT(11) DEFAULT NULL,
     `nocName`            VARCHAR(50) DEFAULT NULL,
     `site`               VARCHAR(50) DEFAULT NULL,
     `machine`            VARCHAR(64) DEFAULT NULL,
     `clientversion`      VARCHAR(20) DEFAULT NULL,
     `nid`                INT(10) DEFAULT NULL,
     `nocStatus`          VARCHAR(250) DEFAULT NULL,
     `consoleId`          INT(11) DEFAULT NULL,
     `timeExecuted`       VARCHAR(100) DEFAULT NULL,
     `machineManufacture` VARCHAR(100) DEFAULT NULL,
     PRIMARY KEY (`tid`),
     KEY `consoleindex` (`consoleId`),
     KEY `nitification` (`servertime`, `nid`)
  )
ENGINE=InnoDB
AUTO_INCREMENT=8229683
DEFAULT CHARSET=latin1 
-1
SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   temptest
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.