CODEWITHSUNDEEP
phpmysqlperformance
daveDDD
daveDDD

Reputation: 11

MySql row to be sorted for performance

I have a MySQL server that keeps events in DB, the DB looks like this:

id | epoch_time | type | event_text  | ....
---|------------|------|-------------|-----
01 | 1487671205 | 0    | user-login  | ....
02 | 1487671284 | 0    | user-logout | ....
03 | 1487671356 | 1    | sys_error   | ....
04 | 1487671379 | 0    | user-logout | ....
05 | 1487671389 | 2    | user_error  | ....
06 | 1487671397 | 1    | sys_error   | ....

On the web UI, there is a summery of the last 24 hours events by type, since the DB is keeping 1 year back log of data, there are over 1M records at the moment which makes the site loads very slow (from the obvious reasons).

The SQL query is simple, 

SELECT COUNT(id) as total FROM `eventLog` WHERE `epoch_time` >= (UNIX_TIMESTAMP() - 86400)

My question is - Is there a way to to "tell" MySQL that the epoch_time column is sorted so that once it hits a raw that has:

epoch_time < (UNIX_TIMESTAMP() - 86400)

The query will end.

Thanks

[UPDATE]

Thank you all for your help, I tried to add the index but the performance is still bad (~ 7 - 12 sec' to load the page)

Does it make sense to just keep statistical information just for that ?

Upvotes: 1

Views: 49

Answers (1)

Imanez
Imanez

Reputation: 514

You can set epoch_time as index using:

ALTER TABLE `eventLog` ADD INDEX epoch_time (`epoch_time`)

That will make your query runs much faster,

Upvotes: 1

Related Questions