fistameeny fistameeny - 1 year ago 98
MySQL Question

Get latest row from each row in a set (5M+ rows)

I have 2 tables - sensors and readings. There is a one to many relation from sensors to readings.

I need to query for all rows from sensors and then get the newest (i.e MAX timestamp) data from readings for each row. I've tried with:

SELECT sensors.*, readings.value, readings.timestamp
FROM sensors
LEFT JOIN readings ON readings.sensor_id =
GROUP BY readings.sensor_id

The problem is, I have 6 million rows of data and the query is taking nearly two minutes to execute. Is there a more effecient way I can get hold of the last reading/value for each sensor?

Mjh Mjh
Answer Source

This is how I'd go about the problem:

  • it involves a trigger that populates latest_readings table
  • it involves another table that I named latest_readings.

The table

I made sensor_id unique because I assumed you have one reading per sensor. This can be categorized by types by adding an additional column.

Reason for unique index: we'll be using MySQL's INSERT INTO ... ON DUPLICATE KEY UPDATE to have all the hard work done for us. If there's a reading for a particular sensor, it gets updated - otherwise, it gets inserted (in one query).

You can also make sensor_id a foreign key. I skipped that part.

CREATE TABLE latest_readings (
    id int unsigned not null auto_increment,
    sensor_id int unsigned not null,
    reading_id int unsigned not null,
    primary key(id),
    unique (sensor_id)
) ENGINE = InnoDB;

The trigger

Trigger type is after insert. I will assume that the table is named readings and that it contains sensor_id column. Adjust accordingly.


    TRIGGER `readings_after_insert` AFTER INSERT ON `readings` 
            INSERT INTO readings 
                (sensor_id, reading_id) 

                ON DUPLICATE KEY UPDATE reading_id =


How to query for latest sensor reading

Once more, I assumed what column names were, so adjust accordingly.


FROM readings r

INNER JOIN latest_readings latest
ON latest.sensor_id = r.sensor_id

WHERE r.sensor_id = 12345;

Disclaimer: this is just an example and it probably contains bugs, which means it's not a copy paste solution. If something doesn't work, and it's easy to fix - please do it :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download