SQL query alternative/optimization for LEFT OUTER JOIN

Question

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

Answer 1

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