CODEWITHSUNDEEP
mysql
Antonio Laguna
Antonio Laguna

Reputation: 9292

SQL query that fries the server

I'm having a really weird issue that burns my MySQL server. From my point of view (which is surely wrong), the query is pretty trivial.

I have a table to store PBX events and I try to get the last events for every agent to see his/her situation whenever my application is restarted or whatever.

Whenever I launch, the server goes up to 99% of CPU and lasts about 5 minutes to solve by itself.

It seems that's because the number of records, more than 100,000.

The table is as follows:

CREATE TABLE IF NOT EXISTS `eventos_centralita` (
  `idEvent` int(11) NOT NULL AUTO_INCREMENT,
  `fechaHora` datetime NOT NULL,
  `codAgente` varchar(8) DEFAULT NULL,
  `extension` varchar(20) DEFAULT NULL,
  `evento` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idEvent`),
  KEY `codAgente` (`codAgente`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=105847 ;

And the query is as follows:

SELECT a.* FROM eventos_centralita a 
LEFT JOIN eventos_centralita b ON b.codAgente = a.codAgente AND b.fechaHora > a.fechaHora 
GROUP BY a.codAgente

I've tried to limit it by date but no luck as the query doesn't give me anything. How could I improve the query to avoid this ?

Upvotes: 0

Views: 51

Answers (1)

Minesh
Minesh

Reputation: 2302

Please try below:

SELECT a.* FROM eventos_centralita a 
INNER JOIN 
(
    SELECT idEvent, MAX(fechaHora)
    FROM eventos_centralita  
    GROUP BY codAgente
) as b
ON a.idEvent = b.idEvent

Upvotes: 1

Related Questions