PHP+SQL Select query split to avoid INNER JOIN, problem

predator

I currently have a couple of working SQL queries that select Players that belong to a specific team during a specific season. All fine and dandy, however, since I chosen to use two "small" queries instead of a large query with INNER JOIN etc (for loading purposes) I have now run into a problem. See details below:

//Here I select all PlayerID:s belonging to a team during a specific season (year)
if ($year != "") {
$vsql = "SELECT PlayerID from stats WHERE TeamID='$tID' AND EndDate='$year'";
}
else {
$vsql = "SELECT PlayerID from stats WHERE TeamID='$tID' AND EndDate='$global_enddate' AND Hidden='0'";
}

//I save all data in variable to be used in another SQL query
$stat_result = mysql_query($vsql, $db_link);

$player_list = "";

while ($stat_row = mysql_fetch_row($stat_result)) {

if ($player_list == "")
 {
$player_list = "'{$stat_row[0]}'";
}
else
{
$player_list .= ", '{$stat_row[0]}'";
}
}

//Below I use all saved PlayerIDs to select various player data to show up on the team roster page
mysql_free_result($stat_result);

$vsql = "SELECT PlayerID, Firstname, Lastname, Pos, Pos2, Birth, Birthplace, Length, Weight, 
Rookie, Shoots, Motherclub, NationID, Contract, Caphit, Injured, Injuredinfo FROM player 
WHERE PlayerID IN ($player_list) ORDER BY Pos ASC, Lastname ASC";

$player_result = mysql_query($vsql);

 while ($player_row = mysql_fetch_assoc($player_result))
{
$playID = $player_row['PlayerID'];
$Firstname = $player_row['Firstname'];
etc. etc. 
}

The above query works very well, but here comes the problem:

PROBLEM: From the first query ($vsql = "SELECT PlayerID from stats ...) I also want to select Loan and not only PlayerID. The Loan value is associated with the players and I want it to be visible on the team roster page. $vsql = "SELECT PlayerID, Loan from stats WHERE TeamID='$tID' AND EndDate='$year'";

So my question is; how can I "save" the Loan value so it is "remembered" for each specific player (= all PlayerIDs) in the second query where I select additional player data? Something tells me it is probably not too difficult, but I am not really used to writing queries like above and I have always solved similar things with INNER JOINS.

laserlight

Are you sure that the use of a join would be worse performance wise compared to two queries?

If your measurements show that it is so, then have you created the appropriate database indices?

predator

laserlight;10952065 wrote:
Are you sure that the use of a join would be worse performance wise compared to two queries?

If your measurements show that it is so, then have you created the appropriate database indices?

Yeah, actually that was a bit of a surprise to me. The previous query (a long one with INNER JOIN) had been used for years and as the site started to load slower (due to lots of hits) I was looking at way to optimise some queries. I split the query into two like above and the difference in performance was surprising and very obvious. Now the query has been running like above for a year or so and worked perfectly until now, when I want to make the change I suggested.

laserlight

That certainly is surprising. I am moving this thread to the Database forum in the hope that a database guru may take a look at your problem. I suggest that you post what relevant database indices you used.

In the meantime, my suggestion would be to use an associative array, e.g.:

//Here I select all PlayerID:s belonging to a team during a specific season (year)
if ($year != "") {
    $vsql = "SELECT PlayerID, Loan from stats WHERE TeamID='$tID' AND EndDate='$year'";
}
else {
    $vsql = "SELECT PlayerID, Loan from stats WHERE TeamID='$tID' AND EndDate='$global_enddate' AND Hidden='0'";
}

//I save all data in variable to be used in another SQL query
$stat_result = mysql_query($vsql, $db_link);

$players = array();
$player_list = array();
while ($stat_row = mysql_fetch_row($stat_result))
{
    $player_list[] = $stat_row[0];
    $players[$stat_row[0]] = array('Loan' => $stat_row[1]);
}
$player_list = implode("','", $player_list);

//Below I use all saved PlayerIDs to select various player data to show up on the team roster page
mysql_free_result($stat_result);

$vsql = "SELECT PlayerID, Firstname, Lastname, Pos, Pos2, Birth, Birthplace, Length, Weight,
Rookie, Shoots, Motherclub, NationID, Contract, Caphit, Injured, Injuredinfo FROM player
WHERE PlayerID IN ($player_list) ORDER BY Pos ASC, Lastname ASC";

$player_result = mysql_query($vsql);

while ($player_row = mysql_fetch_assoc($player_result))
{
    $players[$player_row['PlayerID']]['Firstname'] = $player_row['Firstname'];
    // etc
}

Roger_Ramjet

I am also surprised that 2 queries are faster than 1, but not so surprised that you have poor performance overall. You see, if the columns used in the WHERE clause are not indexed then you are forcing a table scan.

If you use EXPLAIN then you will see just what is happening in your particular case.

Now if the columns used in the where clause are also indexed then the server can avoid a table scan. So add indexes to TeamID (probably you already have that) and EndDate. Then run EXPLAIN again and see the difference.

With EXPLAIN what you are looking for is a) anything that says 'table scan', b) the number of rows involved in temp tables and intermediate resultsets. You also want to check that indexes are actually being used, and in the most efficient order.

An alternative to your 2 queries is as follows:

if ($year != "") {
$vsql1 = "SELECT PlayerID from stats WHERE TeamID='$tID' AND EndDate='$year'";
}
else {
$vsql1 = "SELECT PlayerID from stats WHERE TeamID='$tID' AND EndDate='$global_enddate' AND Hidden='0'";
}

$vsql2 = "SELECT PlayerID, Firstname, Lastname, Pos, Pos2, Birth, Birthplace, Length, Weight,
Rookie, Shoots, Motherclub, NationID, Contract, Caphit, Injured, Injuredinfo FROM player
WHERE PlayerID IN ($vsql1) ORDER BY Pos ASC, Lastname ASC";

Here we are doing the same thing without 2 trip to the database.

But that does not solve your extra field problem. For that you can use a sub query in a different way

if ($year != "") {
$vsql1 = "SELECT PlayerID, Loan from stats WHERE TeamID='$tID' AND EndDate='$year'";
}
else {
$vsql1 = "SELECT PlayerID, Loan from stats WHERE TeamID='$tID' AND EndDate='$global_enddate' AND Hidden='0'";
}

$vsql2 = "SELECT PlayerID, Loan, Firstname, Lastname, Pos, Pos2, Birth, Birthplace, Length, Weight,
Rookie, Shoots, Motherclub, NationID, Contract, Caphit, Injured, Injuredinfo FROM player INNER JOIN $vsql1 AS t1 USING(PlayerID) ORDER BY Pos ASC, Lastname ASC";

Here I am using the $vsql1 as a scalar or table-type sub query. It will be executes first and then joined to the outer query. Predicting performance with this type of query is dificult so suck it and see is all I can recommend.

In all cases, add the indexes to columns frequently used in where clauses.