Rank change since last update

[lukep11a]

Hi, I have a leaderboard which ranks each team in descending order of points. I want to get the rank change from an earlier time, I have some code that currently works but it is taking a long time to load the page. Is there a better way to apply the code below? I currently have it set to check the rank against 7 days ago but ideally would like it to show the rank change since the last table update.

 

$query = "SELECT users.user_id, user_teams.user_team_id, user_team_name, first_name, last_name, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date 
		AND team_selections.transfer_out > team_points.date THEN team_points.points ELSE 0 END),0) as total, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < '2012-08-15' THEN team_points.points ELSE 0 END),0) as previous_total, txnid
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY total DESC, user_team_name ASC";
		$result = mysql_query($query) or die(mysql_error());

		$counter = 1;
$user_team_id = $row['user_team_id'];
		$query1 = "SELECT user_teams.user_team_id, user_teams.user_team_name, user_teams.date, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < DATE_ADD(NOW(), INTERVAL -7 DAY) THEN team_points.points ELSE 0 END),0) as previous_total
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY previous_total DESC, user_teams.date ASC";
		$result1 = mysql_query($query1) or die(mysql_error());

		$change_counter = 1;

		$rank = 1; // rank pointer


			$userID=$user_team_id;

					while($row1 = mysql_fetch_assoc($result1)) 
					{
			// check for a match
			if($userID==$row1['user_team_id']){
			$rank=$change_counter; 
			}
			$change_counter++; //increase the value of the counter with 1 
			} // end of loop
			$change = $rank - $counter;

[fenway]

Those queries aren't doing what you think.

 

Post EXPLAINS,  and then explain in words what you're trying to achieve.

[lukep11a]

Sorry I tried to minimise the amount of code I posted but missed some vital parts out, here is the full code:

 

$query = "SELECT users.user_id, user_teams.user_team_id, user_team_name, first_name, last_name, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date 
		AND team_selections.transfer_out > team_points.date THEN team_points.points ELSE 0 END),0) as total, 
		COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < '2012-08-15' THEN team_points.points ELSE 0 END),0) as previous_total, txnid
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY total DESC, user_team_name ASC";
		$result = mysql_query($query) or die(mysql_error());
		?>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr class="tables-header">
                <td>Pos</td>
                <td>Change</td>
                <td>Team Name</td>
                <td>Chairman</td>
                <td>Points</td>
                <td>Paid</td>
              </tr>
            <?php
		$counter = 1;
		while($row = mysql_fetch_assoc($result))
        	{
		if ($row['user_id'] == $_SESSION['user_id']) {
			$link = '../my-account/teams';
			$class = 'tables-myteams'; }
		else {
			$link = '../stats/user-team-data';
			$class = 'tables-teams'; }
		if ($row['txnid'] == "") {
			$paid = 'No'; }
		else {
			$paid = 'Yes'; }

		$user_team_id = $row['user_team_id'];
		$query1 = "SELECT user_teams.user_team_id, user_teams.user_team_name, user_teams.date, COALESCE(SUM(CASE WHEN team_selections.transfer_in < team_points.date AND team_selections.transfer_out > team_points.date AND team_points.date < DATE_ADD(NOW(), INTERVAL -7 DAY) THEN team_points.points ELSE 0 END),0) as previous_total
		FROM user_teams
		LEFT JOIN users
		ON users.user_id = user_teams.user_id
		LEFT JOIN team_selections
		ON user_teams.user_team_id = team_selections.user_team_id
		LEFT JOIN team_points
		ON team_selections.team_id = team_points.team_id
		LEFT JOIN payments
		ON user_teams.user_team_id = payments.user_team_id
		GROUP BY user_team_id
		ORDER BY previous_total DESC, user_teams.date ASC";
		$result1 = mysql_query($query1) or die(mysql_error());

		$change_counter = 1;

		$rank = 1; // rank pointer


			$userID=$user_team_id;

					while($row1 = mysql_fetch_assoc($result1)) 
					{
			// check for a match
			if($userID==$row1['user_team_id']){
			$rank=$change_counter; 
			}
			$change_counter++; //increase the value of the counter with 1 
			} // end of loop
			$change = $rank - $counter;

		?>
            <a name="<?php echo $row['user_team_id']; ?>" id="<?php echo $row['user_team_id']; ?>"></a>
              <tr class="<?php echo $class; ?>">
                <td><?php echo $counter; ?></td>
                <td><span class="<?php echo $mov_img; ?>"></span><?php echo $change; ?></td>
                <td><a href='<?php echo $link; ?>/<?php echo $row['user_team_id']; ?>' title='<?php echo $row['user_team_name']; ?> Team Data'><?php echo $row['user_team_name']; ?></a></td>
                <td><?php echo $row['first_name']." ".$row['last_name']; ?></td>
                <td><?php echo $row['total']; ?></td>
                <td><?php echo $paid; ?></td>
              </tr>
              <?php
		  $counter++; //increase the value of the counter with 1
		  }
		  ?>
            </table>

 

So, the first query selects all the parts needed to fill in the table and orders by the total points in descending order. The second query is within the while statement of the first to calculate the points each team had 7 days before today. This is so I can display how each team's rank has changed from 7 days ago. Ideally I would like to show each team's rank change since the last MySQL update if this is possible.

 

The query works in acheiving what I am trying to do but I am looking for a better solution if there is one as this way is very slow in loading.

[Jessica]

This is why it's bad to run queries in loops. You need to use a JOIN statement.

[lukep11a]

Thanks, my first thought was to put it all in one join statement. But I order by total points to determine the rank so how can I determine the rank of the total points from 7 days ago in the same statement?

[fenway]

Thanks, my first thought was to put it all in one join statement. But I order by total points to determine the rank so how can I determine the rank of the total points from 7 days ago in the same statement?

Again, you haven't told us what you're trying to -- you're telling us why what you're written doesn't work.

[lukep11a]

I am trying to display the change in rank for each team on the leaderboard, the first query selects the required information for the leaderboard. The second query calculates the position each team was in 7 days ago so that I can display the change in rank. The code I posted does work it just takes a long time to load the page so I am looking for a better way to get the same result.

[fenway]

Let's start by looking at EXPLAIN output for both queries.

[lukep11a]

I get this for the first query:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user_teams ALL NULL NULL NULL NULL 115 Using temporary; Using filesort

1 SIMPLE users eq_ref PRIMARY PRIMARY 3 vouche7_tff2012.user_teams.user_id 1

1 SIMPLE team_selections ref user_team_id user_team_id 3 vouche7_tff2012.user_teams.user_team_id 14

1 SIMPLE team_points ALL NULL NULL NULL NULL 462

1 SIMPLE payments ALL NULL NULL NULL NULL 29

 

And this for the second:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user_teams ALL NULL NULL NULL NULL 115 Using temporary; Using filesort

1 SIMPLE users eq_ref PRIMARY PRIMARY 3 vouche7_tff2012.user_teams.user_id 1 Using index

1 SIMPLE team_selections ref user_team_id user_team_id 3 vouche7_tff2012.user_teams.user_team_id 14

1 SIMPLE team_points ALL NULL NULL NULL NULL 462

1 SIMPLE payments ALL NULL NULL NULL NULL 29

[fenway]

team_points.team_id is one of the missing indexes.

 

payments.user_team_id is another.

[lukep11a]

Sorry, could you explain what that means please? is that what's causing it to load slowly?

[fenway]

That means 'create the indexes on the columns above' -- you're currently performing joins on un-indexed columns.

[lukep11a]

Ok thanks for your help, I have created FOREIGN KEYS on the two columns you suggested and it now seems to be loading the page faster