Optimising Database Structure

Question

I'm developing a reward system for our VLE which uses three separate technologies - JavaScript for most of the client-side/display processing, PHP to communicate with the database and MySQL for the database itself.

I've attached three screenshots of my "transactions" table. Its structure, a few example records and an overview of its details.

The premise is that members of staff award points to students for good behaviour etc. This can mean that classes of 30 students are given points at a single time. Staff have a limit of 300 points/week and there are around 85 staff currently accessing the system (this may rise).

The way I'm doing it at the moment, every "transaction" has a "Giver_ID" (the member of staff awarding points), a "Recipient_ID" (the student receiving the points), a category and a reason. This way, every time a member of staff issues 30 points, I'm putting 30 rows into the database.

This seemed to work early on, but within three weeks I already have over 12,000 transactions in the database.

At this point it gets a bit more complicated. On the Assign Points page (another screenshot attached), when a teacher clicks into one of their classes or searches for an individual student, I want the students' points to be displayed. The only way I can currently do this on my system is to do a "SELECT * FROM 'transactions'" and put all the information into an array using the following JS:

var Points = { "Recipient_ID" : "0", "Points" : "0" };

function getPoints (data) {
    for (var i = 0; i < data.length; i++) {
        if (Points[data[i].Recipient_ID]) {
            Points[data[i].Recipient_ID] = parseInt(Points[data[i].Recipient_ID]) + parseInt(data[i].Points);
        } else {
            Points[data[i].Recipient_ID] = data[i].Points;
        }
    }
}

When logging in to the system internally, this appears to work quickly enough. When logging in externally however, this process takes around 20 seconds, and thus doesn't display the students' points values until you've clicked/searched a few times.

I'm using the following code in my PHP to access these transactions:

function getTotalPoints() {
    $sql = "SELECT * 
        FROM `transactions`";

    $res = mysql_query($sql);
    $rows = array(); 
    while($r = mysql_fetch_assoc($res)) {
        $rows[] = $r;
    }

    if ($rows) {
        return $rows;
    } else {
        $err = Array("err_id" => 1);
        return $err;
    }
}

So, my question is, how should I actually be approaching this? Full-text indices; maybe a student table with their total points values which gets updated every time a transaction is entered; mass-transactions (i.e. more than one student receiving the same points for the same category) grouped into a single database row? These are all things I've contemplated but I'd love someone with more DB knowledge than myself to provide enlightenment.

Example records

Table structure

Table overview

Assign Points interface

Many thanks in advance.

Answer 1

Your problem is your query:

SELECT * FROM `transactions`

As your data set gets bigger, this will take longer to load and require more memory to store it. Rather determine what data you need specifically. If it's for a particular user:

SELECT SUM(points) FROM `transactions` WHERE Recipient_ID=[x]

Or if you want all the sums for all your students:

SELECT Recipient_ID, SUM(points) AS Total_Points FROM `transactions` GROUP BY Recipient_ID;

To speed up selections on a particular field you can add an index for that field. This will speed up the selections, especially as the table grows.

ALTER TABLE `transactions` ADD INDEX Recipient_ID (Recipient_ID);

Or if you want to display a paginated list of all the entries in transactions:

SELECT * FROM `transactions` LIMIT [page*num_records_per_page],[num_records_per_page];

e.g.: SELECT * FROM `transactions` LIMIT 0,25 ORDER BY Datetime; # First 25 records