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 
6

1 Answer 1

-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;
4
  • 2
    That usage of GROUP BY is incorrect and only "works" because MySQL chooses to return random data in that case. Any other DBMS will righfully reject it. More details here rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html and here mysqlperformanceblog.com/2006/09/06/… (and you are missing an ORDER BY...)
    – user1822
    Aug 4, 2013 at 16:55
  • but op tagged mysql Aug 4, 2013 at 16:56
  • Yes and MySQL will simply return random data - and most probably not the one that is expected.. Read the links I posted.
    – user1822
    Aug 4, 2013 at 16:57
  • Just becasue you can do something, doesn't mean you should. It is a poor practice to do group bys this way in ALL databases even if you think you can.
    – HLGEM
    Sep 3, 2013 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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