从其他表连接最大值的最佳和最佳方法 [英] Best and optimal way to join max value from other table

问题描述

我想找到运行此查询的最佳方法，这里是细节，可以说以下是我的表及其数据:

I want to find the optimal way to run this query, here the details, lets say the following are my tables with their data:

-- User Table
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

-- Entries
INSERT INTO `users` VALUES (1,'user_a');
INSERT INTO `users` VALUES (2,'user_b');
INSERT INTO `users` VALUES (3,'user_c');

-- User Log Table
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idrel` int(11) NOT NULL,
  `event` varchar(20) NOT NULL,
  `log` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idrel` (`idrel`,`log`)
) ENGINE=MyISAM;

-- Entries
INSERT INTO `user_log` VALUES (1,1,'log1','2014-01-01 13:00:00');
INSERT INTO `user_log` VALUES (2,1,'log2','2014-01-02 13:00:00');
INSERT INTO `user_log` VALUES (3,2,'log3','2014-01-03 13:00:00');
INSERT INTO `user_log` VALUES (4,2,'log4','2014-01-04 13:00:00');
INSERT INTO `user_log` VALUES (5,3,'log5','2014-01-05 13:00:00');
INSERT INTO `user_log` VALUES (6,3,'log6','2014-01-06 13:00:00');

我想查询参加最近记录的事件的用户，我找到了两种方法来执行此操作，但是我不知道哪种方法最好(谈论两个表增长时的速度)或是否还有另一种方法，这是我的查询:

And I want to query the users joining the latest event logged, I have found two ways to do this but I don't know which one is the best (talking about speed when both tables grow) or if there is another way, here are my queries:

-- Query 1
SELECT
    u.id,
    u.name,
    l2.event as last_record
FROM
    users AS u
INNER JOIN
    (
        SELECT
            idrel,
            MAX(id) as last_id
        FROM user_log
        GROUP BY
            idrel
    ) AS l1
    ON (l1.idrel = u.id)
INNER JOIN
        user_log AS l2
        ON (l2.id = l1.last_id);

哪个给我这个结果:

+----+--------+-------------+
| id | name   | last_record |
+----+--------+-------------+
|  1 | user_a | log2        |
|  2 | user_b | log4        |
|  3 | user_c | log6        |
+----+--------+-------------+
3 rows in set (0.00 sec)

-- Query 2
SELECT
    u.id,
    u.name,
    (
        SELECT event FROM user_log WHERE idrel = u.id ORDER BY log DESC LIMIT 1
    ) AS last_record
FROM
    users AS u;

结果:

+----+--------+-------------+
| id | name   | last_record |
+----+--------+-------------+
|  1 | user_a | log2        |
|  2 | user_b | log4        |
|  3 | user_c | log6        |
+----+--------+-------------+
3 rows in set (0.00 sec)

哪个是最好的方法?还有其他更好的方法吗?

Which one could be the best way? Is there any other better way?

推荐答案

不要那样做，这是一种更好的方法，首先，添加一个字段，将任何新日志条目的最后一个ID存储在users表上，然后创建一个触发器，以在插入的每个新字段上更新该字段，然后(可选)创建一个视图，以获取要查询的完整表".

Don’t do it that way, here is a better way, first, add a field to store on the users table the last ID of any new log entry, then create a trigger that updates that field on each new field inserted, then (optional) create a view to have a complete "table" to query from.

-- Add new field to save the modified id
ALTER TABLE `users` ADD `last_mod_id` INT(11) NOT NULL, ADD INDEX (`last_mod_id`);

-- Drop the trigger just in case it exists
DROP TRIGGER IF EXISTS `update_last_id`;

-- Create a trigger to save the last modified id each time a row is created
DELIMITER ;;
CREATE TRIGGER update_last_id 
    AFTER INSERT ON user_log
    FOR EACH ROW
    BEGIN
        UPDATE `users` 
        SET `last_mod_id` = NEW.id
        WHERE `id` = NEW.idrel;
    END;
;;

-- Emtpy your table
TRUNCATE `user_log`;

-- Create all new registries 
INSERT INTO `user_log` VALUES (1,1,'log1','2014-01-01 13:00:00');
INSERT INTO `user_log` VALUES (2,1,'log2','2014-01-02 13:00:00');
INSERT INTO `user_log` VALUES (3,2,'log3','2014-01-03 13:00:00');
INSERT INTO `user_log` VALUES (4,2,'log4','2014-01-04 13:00:00');
INSERT INTO `user_log` VALUES (5,3,'log5','2014-01-05 13:00:00');
INSERT INTO `user_log` VALUES (6,3,'log6','2014-01-06 13:00:00');


-- Query
SELECT
    u.id,
    u.name,
    u.last_mod_id,
    l.event,
    l.log
FROM
    `users` as u
INNER JOIN
    `user_log` as l
    ON (l.id = u.last_mod_id)

-- Drop view if exists
DROP VIEW IF EXISTS `users_log_view`;

-- View
CREATE VIEW `users_log_view` AS 
    SELECT
        u.id,
        u.name,
        u.last_mod_id,
        l.event,
        l.log
    FROM
        `users` as u
    INNER JOIN
        `user_log` as l
        ON (l.id = u.last_mod_id);

-- Query
SELECT * FROM `users_log_view` WHERE `id` = 1;

这篇关于从其他表连接最大值的最佳和最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！