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

i have 2 tables - sensors , readings. there 1 many relation sensors readings.

i need query rows sensors , newest (i.e max timestamp) data readings each row. i've tried with:

select sensors.*, readings.value, readings.timestamp sensors left join readings on readings.sensor_id = sensors.id group readings.sensor_id 

the problem is, have 6 million rows of data , query taking 2 minutes execute. there more effecient way can hold of last reading/value each sensor?

this how i'd go problem:

• it involves trigger populates latest_readings table
• it involves table named latest_readings.

the table

i made sensor_id unique because assumed have one reading per sensor. can categorized types adding additional column.

reason unique index: we'll using mysql's insert ... on duplicate key update have hard work done us. if there's reading particular sensor, gets updated - otherwise, gets inserted (in 1 query).

you can make sensor_id foreign key. skipped 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 after insert. assume table named readings , contains sensor_id column. adjust accordingly.

delimiter $$  create     trigger `readings_after_insert` after insert on `readings`          each row begin             insert readings                  (sensor_id, reading_id)                  values                 (new.sensor_id, new.id)                  on duplicate key update reading_id = new.id                 ;         end; $$  delimiter ; 

how query latest sensor reading

once more, assumed column names were, adjust accordingly.

select r.reading_value  readings r  inner join latest_readings latest on latest.sensor_id = r.sensor_id  r.sensor_id = 12345; 

disclaimer: example , probably contains bugs, means it's not copy paste solution. if doesn't work, , it's easy fix - please :)