SELECT ri. Id, ri report_id, ri. "reason, ri. Topic, ri. Online_at
, ri. Offline_at, ri online_time, ri. Gmt_create, ri. Gmt_modified
The FROM report_info ri
LEFT the JOIN report r ON r.i d=ri. Report_id
WHERE r.d evice_name=?
The ORDER BY ri. Id DESC
LIMIT 1

CodePudding user response:

Want to get report_info according to device_name exterior-interior offline_at time is the latest one record

CodePudding user response:

The SET NAMES utf8mb4; The SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the Table structure for the report -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DROP Table IF the EXISTS ` report `; CREATE TABLE `report` ( `id` int(11) NOT NULL AUTO_INCREMENT, `event` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `product_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `device_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `report_date` date NOT NULL, `gmt_create` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `gmt_modified` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_default`(`product_id`, `device_name`, `report_date`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=35630 CHARACTER SET=utf8 COLLATE=utf8_bin ROW_FORMAT=Dynamic;
The SET FOREIGN_KEY_CHECKS=1;








The SET NAMES utf8mb4; The SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the Table structure for report_info -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DROP Table IF the EXISTS ` report_info `; CREATE TABLE `report_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_id` int(11) NOT NULL, `reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `topic` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `online_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `offline_at` timestamp(0) NULL DEFAULT NULL, `online_time` int(11) NULL DEFAULT NULL, `gmt_create` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `gmt_modified` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_default`(`offline_at`, `online_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=161001 CHARACTER SET=utf8 COLLATE=utf8_bin ROW_FORMAT=Dynamic;
The SET FOREIGN_KEY_CHECKS=1;

CodePudding user response:

Now under the condition of high concurrency, the higher the time is longer, 30 concurrent went away for 5 seconds

CodePudding user response:

The select ri. * from report_info ri inner join
(
Select the id from the report where device_name=?
) r on ri. Report_id=r.i d
The order by ri. Id desc limit 1

CodePudding user response:

Your purpose is: according to the device_name for report_info exterior-interior offline_at time is one of the newest records, if no difference of SQL statements, add the deviceName and reportId index and query of information query their own use, but I don't recommend to do so, I would suggest that you split the SQL with business, first you separate device_name query A table by querying conditions, to obtain A reportId reportId after the lookup table, get the latest data, two-step, in these two steps, if the volume is very big, can join the cache technology, etc

CodePudding user response:

If you can, the two fall table data library, also conveniently to redis cache the latest data

CodePudding user response:

The explain SQL to check the reason first