[Mauzen]

I just finished my biggest single query yet, though I suck at mysql joins and never really worked with big queries yet. So why not post it here (ok, every guy with some experience will rage how inefficient this is, but i dont care cause I already know that)

Код:

SELECT `g1`.`galaxy`, `g1`.`system`, `g1`.`planet`, `g1`.`name`, `g1`.`owner`, `g1`.`ally`, `g1`.`points`, `g1`.`timestamp`, `i1`.`inactive`
FROM galaxydata g1
	JOIN
		(    
		SELECT `p1`.`name`, ($timestamp - `p2`.`timestamp`) AS `inactive`
		FROM
        	        (
                        SELECT `t2`.`name`, `t2`.`gesamtp`, `t2`.`timestamp`
                        FROM playerdata t2
                        WHERE `t2`.`timestamp` = $timestamp
			) p1,
                        playerdata p2
		WHERE `p1`.`name` = `p2`.`name`
			AND `p1`.`gesamtp` = `p2`.`gesamtp`
		GROUP BY `p1`.`name`
		) i1
	ON ( `i1`.`name` = `g1`.`owner` );

Note that $timestamp is a previously acquired variable and you dont need to mind it, for testing just replace it with 1366803483277
What does this actually do? I got two tables, playerdata and galaxydata. Galaxydata contains "planets" with coordinates like <galaxy><system><planet> and (beneath others) the name of the owner.
Playerdata contains a set of player-statistics snapshots for each owner, taken in certain intervals (that timestamp tells the creation time for each snapshot). The join now joins every planet with the "activity" of its owner (means the interval between the $timestamp snapshot, and the oldest snapshot since when the points didnt change). Note that planets can also have no owner, I didnt filter these from the join yet (maybe that can speed it up)

Galaxydata got ~2k lines, later gonna have 50k, playerdata got 20k, might also go up to 50k, and current time per query is roughly 0.15s which is still okay for my needs, but will later grow to a damn long time.

So I dont really mind if someone wants to test his mysql skills, or if Ill optimize it myself later, just wanted to try posting it here. Post your versions and Ill test them and see if they are working, and working faster