Need help Speeding up this Query

Question

I have a program which scans the users that are online on a server and for each user found inserts a new row in a table. This scan occurs once every 5 minutes and the data is used to draw a user-activity graph on a website.

Here is the structure of my table:

-------------------------------------------------------
|                   stats_table                       |
-------------------------------------------------------
| id, bigint(20) unsigned not null PRI auto_increment |
| scan_id, bigint(20) unsigned not null               |
| username, varchar(32) null                          |
| time_scanned, timestamp not null def=curr_timestamp |
-------------------------------------------------------

I want to get the aggregate number of users found since midnight, for each scan.

I have managed to get this, but the query takes over 15 seconds to finish:

SELECT COUNT(*) FROM (SELECT DISTINCT t.scan_id, t1.username FROM
   stats_table INNER JOIN stats_table t1 ON
   t.scan_id >= t1.scan_id WHERE
   t1.time_scanned > CONCAT(DATE(t.time_scanned), ' 00:00:00') AND
   t1.time_scanned > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND
   t1.time_scanned <= NOW()
) s GROUP BY s.scan_id

so I'm wondering if there is a faster way to get this result?

Here is a visual representation on my graph. Blue represents currently online users, and red the aggregate number of users seen so far today:

To clarify, at 17:00 hours 2 users disconnected and then 15 minutes later 2 new users connected to the server for the first time since midnight. You can see how the red line goes from 7 up to 9 to represent this. Similarly, a new user also connected for the first time today at 23:00.

Answer 1

Since I do not see and index definition I'll assume it is not there. What you need to do is add an index on the query you are running:

• http://use-the-index-luke.com/
• http://dev.mysql.com/doc/innodb/1.1/en/innodb-create-index-examples.html
• http://en.wikipedia.org/wiki/Database_index

Note this will most likely cause a slower insert/update.