title: looping through mysql table data
body: <p>I have a mysql table 'table1' with data</p>
  
  <pre><code>id      mailid  currentstatus   assignedto  assignedtime       logtime
  4338    14928   assigned           user1    4/15/2013 13:44    4/15/2013 13:44
  4352    14928   followup           user1    4/15/2013 13:44    4/15/2013 13:50
  16297   14928   assigned           user1    4/15/2013 13:44    4/29/2013 9:52
  16300   14928   replied            user1    4/15/2013 13:44    4/29/2013 9:55
  5731    15710   assigned           user2    4/17/2013 10:16    4/17/2013 10:17
  5769    15710   followup           user2    4/17/2013 10:16    4/17/2013 10:35
  16281   15710   assigned           user2    4/17/2013 10:16    4/29/2013 9:40
  16291   15710   replied            user2    4/17/2013 10:16    4/29/2013 9:48
  </code></pre>
  
  <p>i want to find out the time spent by each user on each mailid.</p>
  
  <p>Explanation of the columns:</p>
  
  <ul>
  <li>id: (Auto Increment Column)</li>
  <li>mailid: (Unique ID for an e-mail)</li>
  <li>currentstatus: (Current Status of the Mail)</li>
  <li>assignedto: (Username to whom the e-mail is assigned to)</li>
  <li>assignedtime: (Date and time the e-mail was assigned)</li>
  <li>logtime: (Date and Time of insert of the row, i.e. date and time of the currentstatus)</li>
  </ul>
  
  <p>The id 4338 and 5731 are the first row for mailid 14928 and 15710 respectively and the last row for these ids are 16300 and 16291 which will always have currentstatus as 'replied'.</p>
  
  <p>Here, i need the output wherein </p>
  
  <blockquote>
    <p>(logtime of 4352 - logtime of 4338) + (logtime of 16300 - logtime of
    16297)</p>
  </blockquote>
  
  <p>This will give me the time spent by user1 on mailid 14928.</p>
  
  <p>What is common here:</p>
  
  <ol>
  <li>the column currentstatus for the last row will always be 'replied'</li>
  <li>each row to be subtracted with the preceding 'assigned' in currentstatus</li>
  </ol>
  
  <p>How is the table updated:</p>
  
  <p>This is a part of an e-mail management solution wherein e-mails are assigned to the users and users can either reply to the e-mail or put it in followup and reply later.</p>
  
  <p>In both the cases below, the users have been assigned an e-mail (14928 and 15710). Both the users had placed these e-mails in followup and when they had to reply to this e-mail, these e-mails were re-assigned and then replied.</p>
  
  <p>Please help!</p>
  
  <p>The desired output is:</p>
  
  <pre><code>mailid  assignedto  timespent
  14928   user1       00:08:55
  15710   user2       00:26:57
  </code></pre>
  
  <p>So far i am able to write the below query, however, need to optimize this query.</p>
  
  <pre><code>SELECT mailid,
         assignedto,
         sum(st) TimeSpent
  FROM
    ( SELECT b.*,
      CASE WHEN b.currentstatus = 'assigned' 
             THEN TIMESTAMPDIFF(SECOND, b.logtime, (SELECT a.logtime
                                                     FROM inbox_log a
                                                     WHERE a.mailid = b.mailid
                                                     AND a.logtime &gt; b.logtime
                                                     ORDER BY a.table1 LIMIT 1))
             ELSE 0
       END st
     FROM table1 b
     WHERE logtime &gt;= '2013-04-25') d
  GROUP BY mailid,
           assignedto;
  </code></pre>
  
  <blockquote>
    <p>Apologies, could not create a fiddle since facing problems with
    <a href="http://sqlfiddle.com" rel="nofollow">http://sqlfiddle.com</a></p>
  </blockquote>
  
  <p>This query takes approx 150 seconds for 16,000 rows. Any suggestions how i can optimize this query</p>