mysql select most recent records that match a given status

Question

I have a database table containing devices and their status ..up or down. The table gets updated from a device monitoring app when a device changes status.

CREATE TABLE device_status( 
time TIMESTAMP(6) NOT NULL, 
device_name VARCHAR(10) NOT NULL,   
type ENUM('up', 'down') NOT NULL,   
device_status_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO device_status VALUES    
('2020-07-01 09:00:00.000001','device1','down',NULL),   
('2020-07-01 09:00:05.000001','device2','down',NULL),   
('2020-07-01 10:00:00.000001','device3','down',NULL),   
('2020-07-01 10:00:05.000001','device1','up',NULL), 
('2020-07-01 10:00:08.000001','device1','down',NULL),   
('2020-07-01 11:00:00.000001','device2','down',NULL),   
('2020-07-01 11:00:05.000001','device1','up',NULL); 

    mysql> select * from device_status;
+----------------------------+-------------+------+------------------+
| time                       | device_name | type | device_status_id |
+----------------------------+-------------+------+------------------+
| 2020-07-01 09:00:00.000001 | device1     | down |                1 |
| 2020-07-01 09:00:05.000001 | device2     | down |                2 |
| 2020-07-01 10:00:00.000001 | device3     | down |                3 |
| 2020-07-01 10:00:05.000001 | device1     | up   |                4 |
| 2020-07-01 10:00:06.000001 | device2     | up   |                5 |
| 2020-07-01 10:00:08.000001 | device1     | down |                6 |
| 2020-07-01 11:00:00.000001 | device2     | down |                7 |
| 2020-07-01 11:00:05.000001 | device1     | up   |                8 |
+----------------------------+-------------+------+------------------+
8 rows in set (0.00 sec)

I want to create a query that shows the current down devices. The output for above table should show something like:

+----------------------------+-------------+------+------------------+
| time                       | device_name | type | device_status_id |
+----------------------------+-------------+------+------------------+
| 2020-07-01 11:00:00.000001 | device2     | down |                7 |
| 2020-07-01 10:00:00.000001 | device3     | down |                3 |
+----------------------------+-------------+------+------------------+
2 rows in set (0.00 sec)

I have 700 devices with approx 5k records being added to table daily. Any help much appreciated !

ok so this query does produce the required result but as the the table has grown it takes a long long time ..current table has approx 50k rows and takes about 5 mins :-(

mysql> select t1.*  
    -> from device_status t1    
    -> left outer join device_status t2 
    -> on (t1.device_name = t2.device_name and t1.time < t2.time)   
    -> where (t2.device_name is null and t1.type = 'down')  
    -> order by device_name;    
+----------------------------+-------------+------+------------------+  
| time                       | device_name | type | device_status_id |  
+----------------------------+-------------+------+------------------+  
| 2020-07-01 11:00:00.000001 | device2     | down |                7 |  
| 2020-07-01 10:00:00.000001 | device3     | down |                3 |  
+----------------------------+-------------+------+------------------+  
2 rows in set (0.00 sec)

i have added indexes to columns as suggested:

mysql> describe device_status;  
+------------------+-------------------+------+-----+----------------------+--------------------------------+   
| Field            | Type              | Null | Key | Default              | Extra                          |   
+------------------+-------------------+------+-----+----------------------+--------------------------------+   
| time             | timestamp(6)      | NO   | MUL | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |   
| device_name      | varchar(10)       | NO   | MUL | NULL                 |                                |   
| type             | enum('up','down') | NO   | MUL | NULL                 |                                |   
| device_status_id | int(10) unsigned  | NO   | PRI | NULL                 | auto_increment                 |   
+------------------+-------------------+------+-----+----------------------+--------------------------------+   
4 rows in set (0.02 sec)

i have also added 12k additional records:

mysql> SELECT COUNT(*) FROM device_status;
+----------+
| COUNT(*) |
+----------+
|    12002 |
+----------+

1 row in set (0.01 sec)

Now if i run my original query:

mysql> select t1.*
    -> from device_status t1
    -> left outer join device_status t2
    -> on (t1.device_name = t2.device_name and t1.time < t2.time)
    -> where (t2.device_name is null and t1.type = 'down')
    -> order by device_name;
+----------------------------+-------------+------+------------------+
| time                       | device_name | type | device_status_id |
+----------------------------+-------------+------+------------------+
| 2020-08-31 15:48:58.597929 | device1     | down |            12001 |
| 2020-08-31 15:48:58.677924 | device2     | down |            12002 |
+----------------------------+-------------+------+------------------+
2 rows in set (23.56 sec)

So i guess i am doing something wrong here ..all help much appreciated !

Try using `order by` on your timestamp column. – khari-sing Aug 31 '20 at 10:57 — khari-sing, Aug 31 '20 at 10:57

Akhilesh Mishra · Accepted Answer · 2020-08-31T15:01:03.530

1

For MySql 8.0 you can use row_number() window function

select time, device_name, type, device_status_id
from (
select *, 
row_number() over (partition by device_name order by time desc) rn
from device_status
) t where rn=1 and type='down'

EDIT - in case of MySql 5.7 or prior then

select time, device_name, type, device_status_id from (
  SELECT    *,
              IF(device_name=@last,@_seq:=@_seq+1,@_seq:=1) AS rn,
              @last:=device_name
    FROM      device_status , (SELECT @_seq:=1, @last:=0) r
    ORDER BY  device_name, time desc) t 
    where rn=1 and type='down';

Also use proper indexing of your table which will improve the performance.

edited Aug 31 '20 at 15:01

answered Aug 31 '20 at 10:54

Akhilesh Mishra

5,876
3
16
32

many thanks for your answer ..unfortunately my database is pre Version 8.0. – user2149346 Aug 31 '20 at 13:25
@user2149346 i have updated the answer for prior versions also. – Akhilesh Mishra Aug 31 '20 at 15:02
Thats fantastic Akhilesh ..however when i run i get: ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (cp850_general_ci,IMPLICIT) for operation '=' ..i am checking how to get around this ! – user2149346 Aug 31 '20 at 19:01
@user2149346 Refer this [Link](https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error) for your error. – Akhilesh Mishra Sep 01 '20 at 05:20

mysql select most recent records that match a given status

1 Answers1