subquery vs join

[Fog Juice]

Hello,

 

I seem to be stuck with a mysql query that runs very long because of a subquery (I know why, just not sure of how to work around the problem). I was hoping someone could help me fix this by making a suggestion on how to use a join. I've never used JOIN, LEFT JOIN, etc so I'd appreciate if someone could give me a little guidance.

 

The query I'm stuck with is this:

SELECT ga.name, g.uuid,  g.id, g.game_id, g.uuid, g.date, ml.x, ml.y, ml.z, r.region_name, g.name as game_name, gt.id as game_type, gt.name as game_type_name, ga.icon
FROM games_owned g, games ga, reg r,  locations ml, game_types gt $pfrom
WHERE g.owner_id = '".cleanup('', $_SESSION['user_id'])."' AND
ga.id = g.game_id AND 
ml.game_id = g.id AND
gt.id = ga.game_type_id AND
ml.date = (SELECT MAX(date) FROM locations WHERE game_id = g.id) AND
ml.region_id = r.id $filter
GROUP BY g.id 
ORDER BY g.date DESC;

 

Any suggestions on how to improve this query would be greatly appreciated, the database has tens of thousands of records so sql efficiency is needed. There is a little bit of PHP code in this example but if you can please help me without using PHP, that would be great.

 

 

Thanks.

[Fog Juice]

Anyone.. please? I'm trying to learn this join/left join business but I just can't grasp it with online tutorials which all seem to be shit for what I'm trying to do. I'd really appreciate it if someone could give me a little help.

[corbin]

You can (and should) convert the:

 

SELECT a.*, b.* FROM a, b WHERE a.blah = b.blah

 

 

Into JOINs, but you're going to be stuck with the subquery because of the MAX() (assuming I understand your table structure correctly).

 

 

 

[kickstart]

Hi

 

I would use something like this (excuse any typos, and not sure what $pfrom contains so ignored that for now):-

 

SELECT ga.name, g.uuid,  g.id, g.game_id, g.uuid, g.date, ml.x, ml.y, ml.z, r.region_name, g.name as game_name, gt.id as game_type, gt.name as game_type_name, ga.icon
FROM games_owned g, 
INNER JOIN games ga ON ga.id = g.game_id 
INNER JOIN locations ml ON ml.game_id = g.id 
INNER JOIN reg r ON ml.region_id = r.id $filter  
INNER JOIN game_types gt ON gt.id = ga.game_type_id 
INNER JOIN (SELECT game_id, MAX(date) AS maxDate FROM locations GROUP BY game_id) Deriv1 ON Deriv1.game_id = g.id AND ml.date = Deriv1.maxDate
WHERE g.owner_id = '".cleanup('', $_SESSION['user_id'])."' 
GROUP BY g.id 
ORDER BY g.date DESC;

 

All theb est

 

Keith