CODEWITHSUNDEEP
mysqlsqlquery-optimization
Mohamad Najib
Mohamad Najib

Reputation: 49

Complex Max Statement MySQL

I've a single query to get 10 Maximum value from MySQL database. It's returned a correct value, but the problem is, it took long time to get the value

This value is CPU data from a device. So, from a single device have about 6 Module Type. From each Module Type have about 120 Module Number. From each Module Number have 2 Slot (active and standby). Module Number places in a subrack. I need to get 10 Module Number which has highest value.

I've tried using my own query, it returned correct value, but the slot is incorrect. Then I found a query from stack overflow (MySQL query, MAX() + GROUP BY)

This is my table structure :

Create Table: CREATE TABLE `Router_Modul_CPU` (
  `date_id` date NOT NULL,
  `hour_id` time NOT NULL,
  `NE` varchar(50) NOT NULL,
  `modul_number` int(11) NOT NULL,
  `modul_type` varchar(50) NOT NULL,
  `slot` int(11) NOT NULL,
  `subrack` int(11) DEFAULT NULL,
  `mean_memory` float DEFAULT NULL,
  `peak_memory` float DEFAULT NULL,
  PRIMARY KEY (`date_id`,`hour_id`,`NE`,`modul_number`,`modul_type`,`slot`),
  KEY `index_key` (`date_id`,`hour_id`,`NE`,`modul_number`,`modul_type`,`slot`,`subrack`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This is my query :

select
    Router_Modul_CPU.NE,
    Router_Modul_CPU.modul_number,
    Router_Modul_CPU.slot,
    Router_Modul_CPU.subrack
from Router_Modul_CPU
inner join
(
    select modul_number, max(peak_memory) as maks
    from `Router_Modul_CPU`
    group by modul_number, NE, subrack
) maxt on
   (Router_Modul_CPU.modul_number = maxt.modul_number and
    Router_Modul_CPU.peak_memory = maxt.maks)
 where modul_type='SPU' and NE='R-D5-SBT' and date_id='2019-02-14'
limit 10

It took about 40-50 seconds to get the data, and those query only for one device

All this data were inserted every 5 minutes to this table. Now this table have about 25 million row.

Is there any tuning to my table or any suggestion for my query to make more efficient?

Thanks

Upvotes: 2

Views: 149

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

There are several problems with your current query. First, the GROUP BY clause in the subquery does not make much sense. Second, you are using LIMIT without ORDER BY in the outer query, which also does not make sense. I suggest the following version:

SELECT
    r1.NE,
    r1.modul_number,
    r1.slot,
    r1.subrack
FROM Router_Modul_CPU r1
INNER JOIN
(
    SELECT modul_number, MAX(peak_memory) AS maks
    FROM Router_Modul_CPU
    GROUP BY modul_number
) r2
    ON r1.modul_number = r2.modul_number AND
       r1.peak_memory = r2.maks
WHERE
    r1.modul_type = 'SPU' AND
    r1.NE = 'R-D5-SBT'    AND
    r1.date_id = '2019-02-14'

I have omitted the LIMIT clause, and if you want to use one, then you will have to provide an ORDER BY clause as well.

Regarding how to make this perform better, one way would be to add the following index to the Router_Model_CPU table:

CREATE INDEX your_idx ON Router_Model_CPU (modul_number, peak_memory);

With this index in place, MySQL might likely choose to join to it rather than to your current subquery. The index should work and be used because the subquery is just asking for the max value of peak_memory per group.

Upvotes: 3

Related Questions