LEFT OUTER JOIN 的 SQL 查询替代/优化

发布于 2024-09-18 06:10:58 字数 641 浏览 9 评论 0原文

我有以下查询：

SELECT
    `pokemon_moves`.`pokemon_move_method_id`,
    `pokemon_moves`.`level`,
    `move`.`id`,
    `move`.`name`
FROM
    `pokemon_moves`
LEFT OUTER JOIN
    `moves` `move` ON
        `move`.`id` = `pokemon_moves`.`move_id`
WHERE
    `pokemon_moves`.`pokemon_move_method_id` < '4' AND
    `pokemon_moves`.`pokemon_id` = '2' AND
    `pokemon_moves`.`version_group_id` = '6'
ORDER BY
    CAST(`pokemon_moves`.`level` as INTEGER) ASC,
    `move`.`name` ASC

它有点慢，我认为这是因为在 moves 表中查询 pokemon_moves 表中的每一行，而不是仅查询符合 WHERE 子句的行。编写此查询的更好选择是什么？

请注意，此（外部）表中的整数存储为文本

原文

I have the following query:

SELECT
    `pokemon_moves`.`pokemon_move_method_id`,
    `pokemon_moves`.`level`,
    `move`.`id`,
    `move`.`name`
FROM
    `pokemon_moves`
LEFT OUTER JOIN
    `moves` `move` ON
        `move`.`id` = `pokemon_moves`.`move_id`
WHERE
    `pokemon_moves`.`pokemon_move_method_id` < '4' AND
    `pokemon_moves`.`pokemon_id` = '2' AND
    `pokemon_moves`.`version_group_id` = '6'
ORDER BY
    CAST(`pokemon_moves`.`level` as INTEGER) ASC,
    `move`.`name` ASC

It is kinda slow and I think that's because the moves table is queried for every row in the pokemon_moves table instead of only the ones that comply to the WHERE clause. What would be a better option to write this query?

Please note that the integers in this (external) table are stored as text

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

阪姬 2024-09-25 06:10:58

尝试使用 INNER JOIN，并将 WHERE 条件放入 JOIN 语句中。例如：

SELECT
    `pokemon_moves`.`pokemon_move_method_id`,
    `pokemon_moves`.`level`,
    `move`.`id`,
    `move`.`name`
FROM
    `pokemon_moves`
INNER JOIN
    `moves` `move` ON
    `pokemon_moves`.`pokemon_id` = '2' AND
    `pokemon_moves`.`version_group_id` = '6' AND
    `pokemon_moves`.`pokemon_move_method_id` < '4' AND
    `move`.`id` = `pokemon_moves`.`move_id`
ORDER BY
    CAST(`pokemon_moves`.`level` as INTEGER) ASC,
    `move`.`name` ASC

Try using an INNER JOIN, and put your WHERE conditions into the JOIN statement. For example:

SELECT
    `pokemon_moves`.`pokemon_move_method_id`,
    `pokemon_moves`.`level`,
    `move`.`id`,
    `move`.`name`
FROM
    `pokemon_moves`
INNER JOIN
    `moves` `move` ON
    `pokemon_moves`.`pokemon_id` = '2' AND
    `pokemon_moves`.`version_group_id` = '6' AND
    `pokemon_moves`.`pokemon_move_method_id` < '4' AND
    `move`.`id` = `pokemon_moves`.`move_id`
ORDER BY
    CAST(`pokemon_moves`.`level` as INTEGER) ASC,
    `move`.`name` ASC

回复收藏 0 原文

~没有更多了~