主页 / dba / 问题 / 43837
Accepted
Jeffrey
Asked: 2013-06-05 14:24:46 +0800 CST

选择唯一值，而时间以最优化的方式最长

  • 772

给定一个简单的文本和时间列，我想从文本列中选择 X 个唯一值，而该行包含时间的最高值。

INSERT INTO `test` (`id`, `text`, `time`) 
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

满足我大部分要求的查询是：

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

对于小桌子，这非常有效。尽管在我的表（300k+ 行）中，由于子查询，它使 mysql 崩溃。

是否可以优化此查询？如果无法优化，是否可以选择最后插入的唯一值text？（理论上id和time是不相关的，尽管在 99% 的情况下会发现相关性，而 id 越高，时间越长）

mysql-5.5

1 个回答

  1. Best Answer

    此查询的明显索引是 on (test,time)。添加它：

    ALTER TABLE test
  ADD INDEX text_time_IX          -- choose a name for the index
    (test,time) ;

    如果您使用此版本的查询（推动派生表内的限制），您还可以获得效率提升：

    SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`
    ORDER BY
        `text` ASC
    LIMIT 0,50 ) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    `text` ASC ;

    如果相同的最大值time出现在多行相同的情况下text，它不会显示完全相同的结果，并且会返回超过 50 行（但我认为这个版本更合适）。如果你真的想要在关系的情况下任意选择，你也可以LIMIT在外部选择中添加。

    • 1

相关问题