从大记录表获取在线用户

Question

i have a big data record from onlines table. (more than 40 Million record)

now i want to show online user in any time from the table but this execute from server has been failed ...

for example when i send request get online in last week , it's dose not work (because the table have very large record).

this is my example php code:

$d = $_GET['date'];
$time   = time() - 60*60 * 24 * $d;
$phql   = "SELECT DISTINCT aid FROM onlines WHERE time > '$time'";

so, do you have any better tips ?

Tnx.

Answer 1

Use EXPLAIN SELECT ... to see which indices are defined on your table. Ensure that particularly for big tables, that your columns that you query are indexed. In this case time.

You can create an index by:

CREATE INDEX time_index ON onlines (time);

This should speed up the query. If you do not care about potential data loss or persistance you might look into using an in-memory table to avoid IO. That speed up queries significantly but will empty the table if the server restarts or MySQL is shut down.