I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields including
id
for primary key
log_time
index
and other 8 columns.
When I perform a select query sorted by log_time
with offset of some million, the query gets a lots of time to process.
My Query:
SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;
Explain
mysql> explain SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | securityData | ALL | NULL | NULL | NULL | NULL | 2664456 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
Table Details
mysql> explain securityData;
+-----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| log_time | double(18,6) unsigned | NO | MUL | NULL | |
/// more fields
+-----------+-----------------------+------+-----+---------+----------------+
The time of processing go higher and higher when I increase the offset value. I tried in both MyISAM and INNODB
How could I achieve the performance of this query?
Aucun commentaire:
Enregistrer un commentaire