SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Florida
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Merging of Large Queries

    A bit of code:

    Code:
    	// Personal bests - returns PB in form of an array, $personalBest[shortname]
    	$personalBest = array();
    	$result_pb = $DB_site->query("
    		SELECT gamename, MAX(score) AS score FROM " . TABLE_PREFIX . "gamesessions AS gamesessions
    		WHERE valid=1 AND $sessionclause
    		AND userid=$bbuserinfo[userid] 
    		GROUP BY gamename
    	");
    	while ($bestArray = $DB_site->fetch_array($result_pb)) {
    		$personalBest[$bestArray[gamename]] = $bestArray[score];
    	}
    	$DB_site->free_result($result_pb);
    
    	// Query selects all the top scores from the database
    	$result_topscores = $DB_site->query("
    		SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM " . TABLE_PREFIX . "gamesessions AS gamesessions
    		WHERE valid=1 AND $sessionclause
    		GROUP BY gamename
    	");
    		
    	// While loop, where part of an SQL query is constructed
    	while ($thesession = $DB_site->fetch_array($result_topscores))
    	{
    		$where[] = "(gamename='$thesession[gamename]' AND score='$thesession[score]')";
    		$playedcounter[$thesession[gamename]] = $thesession['played'];
    	}
    	$DB_site->free_result($result_topscores);
    	// Query built
    	if(!empty($where)) {
    		$whereclause = "WHERE ".implode(' OR ', $where);
    	}

    Is this as optimized as possible? Been looking to convert into a single query but ive had no success at it, and am forced (ugh) to break it up. Would that be correct, or is their something im missing?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    more optimized by combining the two queries? it can be done, but whether it would be faster, i cannot predict, you will have to run some tests and timings
    Code:
    select gamename
         , max(score)     as max_overall_score 
         , max(
           case when userid=$bbuserinfo[userid] 
                then score else null end
              )           as max_player_score 
         , count(*)       as overall_played
         , sum(
           case when userid=$bbuserinfo[userid] 
                then 1 else 0 end
              )           as player_played
      from " . TABLE_PREFIX . "gamesessions
     where valid=1 
       and $sessionclause
    group 
        by gamename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •