代码之家 › 专栏 › 技术社区 › jheddings

处理后加入选择

join sqlite optimization sql

jheddings · 技术社区 · 15 年前

给定以下架构:

CREATE TABLE players (
  id BIGINT PRIMARY KEY,
  name TEXT UNIQUE
);

CREATE TABLE trials (
  timestamp TIMESTAMP PRIMARY KEY,
  player BIGINT,
  score NUMERIC
);

我如何创建 SELECT 首先从中找出最好的分数 trials ,然后加入 name 场从 users 是吗?使用此查询后,我可以获得分数:

SELECT * FROM trials GROUP BY player ORDER BY score ASC LIMIT 10;

我对返回前10名分数的查询如下:

CREATE VIEW top10place AS
  SELECT player.name AS name, trial.*
    FROM trials AS trial, players AS player
    WHERE trial.player = player.id
      AND trial.score = (
        SELECT MAX(score)
        FROM trials AS tsub
        WHERE tsub.player = trial.player
      )
    ORDER BY trial.score DESC, trial.timestamp ASC
    LIMIT 10;

但当我在表中找到数千个条目时,数据库性能开始下降。我认为子查询会破坏我的性能。第一个查询(只返回最高分)仍然执行得很好,所以我想知道是否有方法强制 JOIN 选择最高分后发生的操作。

编辑请注意,查询将返回排名前10的玩家,而不仅仅是排名前10的玩家。如果同一个球员有很多高分,他应该只出现一次在前十名名单。

我使用的是sqlite,所以它没有SQL Server或MySQL的一些扩展功能。

4 回复 | 直到 15 年前

Khb 15 年前

不要运行sqlite,希望限制正确。

select players.name, trials.player, trials.timestamp, trials.score from
    (select player, score, timestamp from
    trials order by score desc, timestamp asc limit 10) trials, players
where players.id = trials.player

当做

Silas Snider 15 年前

这是一个你让事情变得比需要的更困难的例子。正确的代码是:

CREATE VIEW top10place AS
  SELECT player.name AS name, trial.*
    FROM trials AS trial, players AS player
    WHERE trial.player = player.id
    ORDER BY trial.score ASC, trial.timestamp ASC
    LIMIT 10;

基本上,让限制语句来完成工作:)

Andomar 15 年前

如果优化器为每一行运行一个子查询,那么其中的子查询可能会很昂贵。

(编辑)这里有另一种方法来编写查询,现在使用独占联接:它表示该用户没有得分更高的行:

SELECT 
     (select name from user where id = cur.userid) as UserName
,    cur.score as MaxScore
FROM trails cur
LEFT JOIN trials higher
    ON higher.userid = cur.userid
    AND higher.timestamp <> cur.timestamp
    AND higher.score > cur.score
WHERE higher.userid is null
ORDER BY cur.score DESC
LIMIT 10

这将返回得分最高的10个用户。如果你想得到10个最高分数,不管用户是谁,检查塞拉斯的答案。

Myles 15 年前

如前所述,由于您在玩家和测试之间的识别键是player.id和trials.player,所以您应该有trials.player的索引。尤其是如果你经常把这两张桌子联系在一起。

您也可以尝试使您的查询更像。

SELECT p.name as name, t.* FROM players as p
INNER JOIN (SELECT * FROM trials WHERE trials.score = (SELECT MAX(score) FROM trials as tsub WHERE tsub.player = trials.player) LIMIT 10) as t ON t.player = p.id
ORDER BY t.score DESC, t.timestamp ASC

这甚至可以再优化一点,但是如果没有数据来抛出查询,我就不擅长这一点。