Table grouping statistics of 600000 data in MySQL5.7 is very slow.

I have a table service_message that contains about 600000 pieces of data, which records when different devices receive messages:

SHOW CREATE TABLE service_message

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT "Id",
  `property_id` INT(11) NOT NULL COMMENT "Id",
  `value` TEXT NOT NULL COMMENT "",
  `time` DATETIME NOT NULL COMMENT "",
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `time` (`time`),
  KEY `property_id` (`property_id`)
) ENGINE=INNODB AUTO_INCREMENT=844512 DEFAULT CHARSET=utf8

idsql:

3~4

explain:

so. My question is, is there any way to optimize it?

Mysql-optimizes mysql query-optimizes

Mar.28,2021

. Cancel the default sorting: ` sql SELECT device_id,MAX ( time ) AS time FROM
```
   service_message GROUP BY device_id order by null; ```
```
if you don't need real-time results, cache the query results.

it seems that the larger the id, the bigger the time, so you might as well think differently

SELECT
    device_id,
    `time`
FROM
    service_message
WHERE
    id IN (
        SELECT
            max(id)
        FROM
            service_message
        GROUP BY
            device_id
    )

this table has more than 60 w of data, and it is also growing, so it is not recommended to use dynamic queries to get results. Can you create a statistical table with another key

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT 'Id',
  `last_time` DATETIME NOT NULL COMMENT '',
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `last_time` (`time`),
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

in the way of using scripts, the calculation records are recorded in the table at the time of each day, and the statistical tables can be directly queried the next time you view the results.

it should also be quick to build a joint index of device_id and time.

Previous: Vue project. When you switch routes, one of the pages jumps to other pages, but the path changes and the content of the page appears very slowly.

Next: Is it correct to obtain the file creation and modification time in PHP?

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e389fc-444cc.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e389fc-444cc.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?