Speed up MySQL Query with join

Question

I'm having a little performance issue and I think it may be solved with a better query.

The MySQL table has right now 33,000 entries with following scheme: ID - kompID (foreign key) - time (Timestamp) - amountIO - amountNIO - status0 - .... - status8

Every few seconds a new Report like the above is inserted and they are deleted when older than 5 days.

For monitoring purposes I need to have the newest entry for every kompID. This for I found the idea with a join here on Stackoverflow:

SELECT 
     r1.kompID As ID, 
     (SELECT Name 
         FROM Komponenten 
         WHERE Komponenten.KompID = ID) 
         AS Name,
     r1.time, 
     r1.status0, 
     ...
     FROM Reports r1 
     LEFT JOIN Reports r2 
        ON (r1.kompID = r2.kompID AND r1.RepID < r2.RepID)
     WHERE r2.RepID IS NULL;

It works, but with a full database the query needs 150 seconds (on 1 vCore). I could increase the cores, but I'd like to understand what may be done better.

The Output looks like:

ID  Name    time                 status0    ....

1   470-U1  2015-08-24 14:00:30     2   ...

2   420-C   2015-08-24 14:00:33     0   ...

If there isn't any faster query, i could also first select all ComponentIDs and fire a new query for each selecting the newest entry.

So thanks in advance for your help.

EDIT: The SQL Structure

-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 24. Aug 2015 um 16:19
-- Server Version: 5.5.44-0ubuntu0.14.04.1
-- PHP-Version: 5.5.9-1ubuntu4.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `AMS`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `Reports`
--

CREATE TABLE IF NOT EXISTS `Reports` (
  `RepID` int(11) NOT NULL AUTO_INCREMENT,
  `kompID` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `AnzahlIO` int(11) NOT NULL DEFAULT '0',
  `AnzahlNIO` int(11) NOT NULL DEFAULT '0',
  `status0` int(11) NOT NULL,
  `status1` int(11) NOT NULL DEFAULT '0',
  `status2` int(11) NOT NULL DEFAULT '0',
  `status3` int(11) NOT NULL DEFAULT '0',
  `status4` int(11) NOT NULL DEFAULT '0',
  `status5` int(11) DEFAULT '0',
  `status6` int(11) NOT NULL DEFAULT '0',
  `status7` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`RepID`),
  KEY `FK_KomponentID` (`kompID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53447 ;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `Reports`
--
ALTER TABLE `Reports`
  ADD CONSTRAINT `FK_KomponentID` FOREIGN KEY (`kompID`) REFERENCES `Komponenten` (`KompID`);

DELIMITER $$
--
-- Ereignisse
--
CREATE DEFINER=`root`@`%` EVENT `AutoDeleteOldReports` ON SCHEDULE EVERY 1 DAY STARTS '2015-08-10 10:46:57' ON COMPLETION PRESERVE ENABLE COMMENT 'Reports > 5Tage löschen' DO DELETE LOW_PRIORITY FROM AMS.Reports WHERE time < DATE_SUB(NOW(), INTERVAL 5 DAY)$$

DELIMITER ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Fiddle link is here: http://sqlfiddle.com/#!9/9ed73 (I hope it works like that)

Explain statement: id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY r1 ALL NULL NULL NULL NULL 34560
1 PRIMARY r2 ref PRIMARY,FK_KomponentID FK_KomponentID 4 AMS.r1.kompID 16969 Using where; Using index; Not exists 2 DEPENDENT SUBQUERY Komponenten eq_ref PRIMARY PRIMARY 4 func 1

Answer 1

Okay, I just noticed the solution is quite easy:

SELECT 
(SELECT Name AS Name FROM Komponenten k1 WHERE k1.KompID = r0.kompID), 
time, 
AnzahlIO, 
AnzahlNIO, 
status0 
FROM 
(SELECT * FROM Reports ORDER BY RepID DESC) r0 
GROUP BY kompID  

So simply select with reversed repID (Primary Key with auto increment), then Select this and group by kompID. Like this we have a runtime of 0.001 Seconds.

Thanks a lot for your input!

Answer 2

With this query you have the newest entry for every kompID :

select * from Reports r0 where r0.kompID in (
select r1.kompID from (
select r1.kompID, max(time)
from Reports r1
group by r1.kompID ) );

The "query-core" is obviously the following:

select r1.kompID, max(time)
    from Reports r1
    group by r1.kompID

That simply group by kompID and show you, for every different kompID, the one with max date.

Hope I helped you!