lundi 2 mars 2015

Slow query while selecting a higher offset from millions of rows


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