MYSQL SQL Problem

[greatwhitepine]

I have two tables with the following fields...

user (240,000 rows)
usr_id INTEGER PRIMARY KEY
usr_nme TEXT

fight (130,000 rows)
fght_id INTEGER AUTO_INCREMENT PRIMARY KEY
fght_exp INTEGER
fght_opnt_id INTEGER (FOREIGN KEY ON usr_id)


What is the SQL to determine the user name and user id of each oponent where the last fight with that opponent was worth at least 20 exp?

This takes so long to execute that I have never seen the results (over 5 minutes)...

SELECT u.usr_id, u.usr_nme FROM fight f, user u WHERE f.fght_id IN ( SELECT max( t.fght_id ) FROM fight t GROUP BY t.fght_opnt_id ) AND f.fght_opnt_id = u.usr_id AND f.fght_exp >= 20;

The inner query runs in less than 1 second and returns 47,000 rows.

[bogocles]

SELECT
`user`.`usr_id`,
`user`.`usr_name`
FROM
`user`
INNER JOIN `fight`
ON `user`.`usr_id` = `fight`.`fght_opnt_id`
WHERE
`fight`.`fight_exp` >= 20

Is this what you are looking for? I try to avoid sub-queries as much as possible, preferring temp tables and joins, instead.

If you are still having performance issues, look at your indices.

[greatwhitepine]

No that wont work, it looks at all fights. I only want to look at the last one for each user and then determine if the exp is over 20. Thanks for trying.

I figured it out though...


SELECT
t.fght_exp as last_attack_exp,
u.usr_id as user_id,
u.usr_nme as user_name
FROM
(
SELECT
max( fght_id ) as fght_id
FROM
fight
GROUP BY
fght_opnt_id
) f,
user u,
fight t
WHERE ' .
t.fght_opnt_id = u.usr_id AND
t.fght_id = f.fght_id AND
t.fght_exp >= 20
ORDER BY
t.fght_exp DESC;