SQL: Getting the latest log entry for 80,000 sources?

Question

So I have a table of log sources:

CREATE TABLE logSources (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL DEFAULT '',
) ENGINE=InnoDB;

And one of log entries for each log source:

CREATE TABLE logEntries (
    ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    logSourceID INT UNSIGNED NOT NULL DEFAULT '0',
        FOREIGN KEY (logSourceID) REFERENCES logSources (ID) ON DELETE CASCADE,
    timestamp INT(32) UNSIGNED NOT NULL DEFAULT '0',
    logMessage TEXT,
    PRIMARY KEY (ID),
    KEY logSourceIDThenTimestamp (logSourceID, timestamp)
) ENGINE=InnoDB;

Assuming I have 80,000 logSources and a time t I'd like to create a SELECT statement that gives me 80,000 logEntries. For each logSource I want the newest logEntry older than t.

So for a singe logSource the select would be:

SELECT *
  FROM logEntries
 WHERE logSourceID = ?
   AND timestamp < ?
 ORDER BY timestamp DESC
 LIMIT 1

Each of the logEntries are completely independent. I don't even know where to begin writing such a select.

Executing a SELECT 80,000 times is easy ;-) But how do I do it in one SELECT?

Are your 80,000 sources all of them or a subset? How do you know which sources you'd like? — LordBaconPants, Mar 03 '19 at 20:14
@LordBaconPants: It depends. Sometimes I need all of them. Sometimes there'll be a `WHERE logSourceID IN (a,b,c,d,e,...)` — Peter V. Mørch, Mar 03 '19 at 22:00
Thanks, you're right. This is a duplicate. The other answer was exactly what I needed. — Peter V. Mørch, Mar 04 '19 at 00:13

SQL: Getting the latest log entry for 80,000 sources?

0 Answers0