Optimising a SQL query

Tekno Venus

Tekno Venus

Senior Administrator, Developer
Staff member
Joined
Jul 21, 2012
Posts
7,274
Location
UK
First off, I'm no SQL expert. In fact, I don't really know very much SQL at all, and the below is just something I cobbled together from my limited knowledge. Don't be too mean!

Anyway, I am trying to design an application for my school, for handling scores for sporting events. This is currently done on an excel sheet, but it gets very complex. So I am writing a web app for it using PHP and SQL.

For this app, I am currently designing a front end where it totals up all the points scored per house and displays them. There are 4 houses, Roddick, Whittle, Brunel and Newton.

My DB is set up as follows:

Students

[TABLE="width: 500"]
[TR]
[TD]idStudent
[/TD]
[TD]studentname[/TD]
[TD]studentgender[/TD]
[TD]studentyear[/TD]
[TD]studenthouse[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]foo bar[/TD]
[TD]male[/TD]
[TD]11[/TD]
[TD]roddick[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]foo bar[/TD]
[TD]female
[/TD]
[TD]11[/TD]
[TD]whittle[/TD]
[/TR]
[/TABLE]

athletics_results

[TABLE="width: 500"]
[TR]
[TD]idResult
[/TD]
[TD]idStudent[/TD]
[TD]idEvents[/TD]
[TD]time[/TD]
[TD]position[/TD]
[TD]points[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1:03:56[/TD]
[TD]1[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0:46:12[/TD]
[TD]2[/TD]
[TD]13[/TD]
[/TR]
[/TABLE]

idStudent is a foreign key, as is idEvents.

team_results

[TABLE="width: 500"]
[TR]
[TD]idTeamResult
[/TD]
[TD]idEvents[/TD]
[TD]house[/TD]
[TD]position[/TD]
[TD]points[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]roddick[/TD]
[TD]1[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]whittle[/TD]
[TD]2[/TD]
[TD]13[/TD]
[/TR]
[/TABLE]

idEvents is the foreign key. In team events, only the house is needed, not an individual student name.

events

[TABLE="width: 500"]
[TR]
[TD]idEvents
[/TD]
[TD]EventName[/TD]
[TD]eventType[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]roddick[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]whittle[/TD]
[TD]2[/TD]
[/TR]
[/TABLE]

----

I hope that makes sense. Now, for the front end, I need to total up all the points scored per house to display it in a screen like this:
click for full-size image

Currently, I am doing this very strangly, but it works. This was the only way I could get it to work with my limited knowledge:

PHP: 
$link = mysqli_connect('localhost', 'root', '', 'sportsday') or die("Error: " . mysqli_error($link));

$roddick_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'roddick'") or die ('Unable to run query: '.mysqli_error($link));
$roddick_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'roddick'") or die ('Unable to run query: '.mysqli_error($link));
$brunel_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'brunel'") or die ('Unable to run query: '.mysqli_error($link));
$brunel_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'brunel'") or die ('Unable to run query: '.mysqli_error($link));
$newton_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'newton'") or die ('Unable to run query: '.mysqli_error($link));
$newton_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'newton'") or die ('Unable to run query: '.mysqli_error($link));
$whittle_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'whittle'") or die ('Unable to run query: '.mysqli_error($link));
$whittle_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'newton'") or die ('Unable to run query: '.mysqli_error($link));

$r_row = mysqli_fetch_row($roddick_query);
$r_row_team = mysqli_fetch_row($roddick_query_team);
$roddick_score = $r_row[0] + $r_row_team[0];

$b_row = mysqli_fetch_row($brunel_query);
$b_row_team = mysqli_fetch_row($brunel_query_team);
$brunel_score = $b_row[0] + $b_row_team[0];

$n_row = mysqli_fetch_row($newton_query);
$n_row_team = mysqli_fetch_row($newton_query_team);
$newton_score = $n_row[0] + $n_row_team[0];

$w_row = mysqli_fetch_row($whittle_query);
$w_row_team = mysqli_fetch_row($whittle_query_team);
$whittle_score = $w_row[0] + $w_row_team[0];

As you can tell, I am currently querying the athletics table, and the team table for a sum of points, and then adding them in PHP. However, this is slow. It currently takes about a second to load on a local host, and there's not much in the DB at the moment. What I really need to do is combine the two queries per house into one, more efficient one.

Can any one help me? I hope I have given enough details!

Thanks so much,
Stephen
 
Well, after a little research, I have made improvements.

Before, I was using one query per table, which meant two queries per house. Now I only need one query per house.

My new SQL:

Code: 
SELECT SUM(t.points) as total_points FROM 
    (SELECT points FROM athletics_results 
        JOIN students ON students.idStudent=athletics_results.idStudent 
        WHERE students.studenthouse = 'roddick' 
        UNION ALL 
    SELECT points FROM team_results WHERE house = 'roddick') t

Which means the PHP looks like this:

PHP: 
$link = mysqli_connect('localhost', 'root', '', 'sportsday') or die("Error: " . mysqli_error($link));

$roddick_query = mysqli_query($link, "SELECT SUM(t.points) as total_points FROM (SELECT points FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'roddick' UNION ALL SELECT points FROM team_results WHERE house = 'roddick') t") or die ('Unable to run query: '.mysqli_error($link));
$brunel_query = mysqli_query($link, "SELECT SUM(t.points) as total_points FROM (SELECT points FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'brunel' UNION ALL SELECT points FROM team_results WHERE house = 'brunel') t") or die ('Unable to run query: '.mysqli_error($link));
$newton_query = mysqli_query($link, "SELECT SUM(t.points) as total_points FROM (SELECT points FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'newton' UNION ALL SELECT points FROM team_results WHERE house = 'newton') t") or die ('Unable to run query: '.mysqli_error($link));
$whittle_query = mysqli_query($link, "SELECT SUM(t.points) as total_points FROM (SELECT points FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'whittle' UNION ALL SELECT points FROM team_results WHERE house = 'whittle') t") or die ('Unable to run query: '.mysqli_error($link));

$r_row = mysqli_fetch_row($roddick_query);
$roddick_score = $r_row[0];

$b_row = mysqli_fetch_row($brunel_query);
$brunel_score = $b_row[0];

$n_row = mysqli_fetch_row($newton_query);
$newton_score = $n_row[0];

$w_row = mysqli_fetch_row($whittle_query);
$whittle_score = $w_row[0];

Any other suggestions? This does seem faster though :)

Stephen
 

Has Sysnative Forums helped you? Please consider donating to help us support the site!

Back
Top