From d39212226664baf71e5af60da3d9cbb0c2990356 Mon Sep 17 00:00:00 2001 From: Arun Persaud Date: Tue, 13 Jan 2009 20:42:39 -0800 Subject: BUGFIX: rewrote query for new games old code used a long time to figure out if cards from another game could be reused. The new version should be much faster. --- include/db.php | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'include') diff --git a/include/db.php b/include/db.php index de549a5..e5699f6 100644 --- a/include/db.php +++ b/include/db.php @@ -786,13 +786,18 @@ function DB_get_email_pref_by_uid($uid) function DB_get_unused_randomnumbers($userstr) { - $r = DB_query_array(" SELECT randomnumbers FROM Game". - " WHERE randomnumbers NOT IN". - " (SELECT randomnumbers FROM Game". - " LEFT JOIN Hand ON Game.id=Hand.game_id". - " WHERE user_id IN (". $userstr .")". - " GROUP BY randomnumbers". - " )"); + /* optimized version of this query using temporary tables (perhaps we should use a procedure here?). + First we create a copy of the Game table using just the gameid and the cards. + Then in a second round we delete all the gameids of games where our players are in. + At the end we return only the first entry in the temporary table. + */ + DB_query("DROP TEMPORARY TABLE IF EXISTS gametmp;"); + DB_query("CREATE TEMPORARY TABLE gametmp SELECT id,randomnumbers FROM Game;"); + DB_query("DELETE FROM gametmp WHERE id IN (SELECT game_id FROM Hand WHERE user_id IN (".$userstr."));"); + + $r = DB_query_array("SELECT randomnumbers FROM gametmp LIMIT 1;"); + DB_query("DROP TEMPORARY TABLE IF EXISTS gametmp;"); + if($r) return $r[0]; else -- cgit v1.2.3-18-g5258