title: Database design for optimisation
body: <p>A couple of years ago I designed a reward system for 11-16yo students in PHP, JavaScript and MySQL.</p>
  
  <p>The premise is straightforward:</p>
  
  <ul>
  <li>Members of staff issue points to students under various categories ("Positive attitude and behaviour", "Model citizen", etc)</li>
  <li>Students accrue these points then spend them in our online store (iTunes vouchers, etc)</li>
  </ul>
  
  <h1>Existing system</h1>
  
  <p>The database structure is also straightforward (probably too much so):</p>
  
  <h2>Transactions</h2>
  
  <h3>239,189 rows</h3>
  
  <pre><code>CREATE TABLE `transactions` (
   `Transaction_ID` int(9) NOT NULL auto_increment,
   `Datetime` date NOT NULL,
   `Giver_ID` int(9) NOT NULL,
   `Recipient_ID` int(9) NOT NULL,
   `Points` int(4) NOT NULL,
   `Category_ID` int(3) NOT NULL,
   `Reason` text NOT NULL,
   PRIMARY KEY  (`Transaction_ID`),
   KEY `Giver_ID` (`Giver_ID`),
   KEY `Datetime` (`Datetime`),
   KEY `DatetimeAndGiverID` (`Datetime`,`Giver_ID`),
   KEY `Recipient_ID` (`Recipient_ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=249069 DEFAULT CHARSET=latin1
  </code></pre>
  
  <h2>Categories</h2>
  
  <h3>34 rows</h3>
  
  <pre><code>CREATE TABLE `categories` (
   `Category_ID` int(9) NOT NULL,
   `Title` varchar(255) NOT NULL,
   `Description` text NOT NULL,
   `Default_Points` int(3) NOT NULL,
   `Groups` varchar(125) NOT NULL,
   `Display_Start` datetime default NULL,
   `Display_End` datetime default NULL,
   PRIMARY KEY  (`Category_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  </code></pre>
  
  <h2>Rewards</h2>
  
  <h3>82 rows</h3>
  
  <pre><code>CREATE TABLE `rewards` (
   `Reward_ID` int(9) NOT NULL auto_increment,
   `Title` varchar(255) NOT NULL,
   `Description` text NOT NULL,
   `Image_URL` varchar(255) NOT NULL,
   `Date_Inactive` datetime NOT NULL,
   `Stock_Count` int(3) NOT NULL,
   `Cost_to_User` float NOT NULL,
   `Cost_to_System` float NOT NULL,
   PRIMARY KEY  (`Reward_ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1
  </code></pre>
  
  <h2>Purchases</h2>
  
  <h3>5,889 rows</h3>
  
  <pre><code>CREATE TABLE `purchases` (
   `Purchase_ID` int(9) NOT NULL auto_increment,
   `Datetime` datetime NOT NULL,
   `Reward_ID` int(9) NOT NULL,
   `Quantity` int(4) NOT NULL,
   `Student_ID` int(9) NOT NULL,
   `Student_Name` varchar(255) NOT NULL,
   `Date_DealtWith` datetime default NULL,
   `Date_Collected` datetime default NULL,
   PRIMARY KEY  (`Purchase_ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=6133 DEFAULT CHARSET=latin1
  </code></pre>
  
  <h1>Problems</h1>
  
  <p>The system ran perfectly well for a period of time. It's now starting to slow-down massively on certain queries.</p>
  
  <p>Essentially, every time I need to access a students' reward points total, the required query takes <em>ages</em>. Here is a few example queries and their run-times:</p>
  
  <h3>Top 15 students, excluding attendance categories, across whole school</h3>
  
  <pre><code>SELECT CONCAT( s.Firstname,  " ", s.Surname ) AS  `Student` , s.Year_Group AS `Year Group`, SUM( t.Points ) AS  `Points` 
  FROM frog_rewards.transactions t
  LEFT JOIN frog_shared.student s ON t.Recipient_ID = s.id
  WHERE t.Datetime &gt;  '2013-09-01' AND t.Category_ID NOT IN ( 12, 13, 14, 26 )
  GROUP BY t.Recipient_ID
  ORDER BY  `Points` DESC 
  LIMIT 0 , 15
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/vbnmW.png" alt="EXPLAIN 1"></p>
  
  <ul>
  <li>Run-time: <strong>44.8425 sec</strong></li>
  </ul>
  
  <p><code>SELECT Recipient_ID, SUM(points) AS Total_Points FROM</code>transactions<code>GROUP BY Recipient_ID</code></p>
  
  <p><img src="https://i.stack.imgur.com/j7ePa.png" alt="EXPLAIN 2"></p>
  
  <ul>
  <li>Run-time: <strong>9.8698 sec</strong></li>
  </ul>
  
  <p>Now I appreciate that, especially with the second query, I shouldn't ever be running a call which would return such a vast quantity of rows but the limitations of the framework within which the system runs meant that I had no other choice if I wanted to display students' total reward points for teachers/tutors/year managers/leadership to view and analyse.</p>
  
  <h1>Time for a solution</h1>
  
  <p>Fortunately the framework we've been forced to use is changing. We'll now be using oAuth rather than a horrible, outdated JavaScript widget format.</p>
  
  <p>Unfortunately - or, I guess, fortunately - it means we'll have to rewrite quite a lot of the system.</p>
  
  <p>One of the main areas I intend to look at when rewriting the system is the database structure. As time goes on it will only get bigger, so I need to do a bit of future-proofing.</p>
  
  <p>As such, my main question is this: <strong>what is the most efficient and effective way of storing students' point totals?</strong></p>
  
  <p>The only idea I can come up with is to have a separate table called <code>totals</code> with <code>Student_ID</code> and <code>Points</code> fields. Every time a member of staff gives out some points, it adds a row into the <code>transactions</code> table but also updates the <code>totals</code> table.</p>
  
  <p>Is that efficient? Would it be efficient to also have a <code>Points_Since_Monday</code> type field? How would I update/keep on top of that?</p>
  
  <p>On top of the main question, if anyone has suggestions for general improvement with regard to optimisation of the database table, please let me know.</p>
  
  <p>Thanks in advance,
  Duncan</p>