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&gt; 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&gt; 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?
You're seeing the intrinsic drawback of OFFSET pagination. Using LIMIT, you force your DB to sort all the records and count them all the way up to your OFFSET base. 

Keyset pagination may correspond more closely to your actual needs here and will yield much better performance. As described in the above linked article, simply paginate based on the last key you've seen:

SELECT * 
FROM securityData 
WHERE id &gt; ?last_seen_id
ORDER BY log_time 
LIMIT 50;


This allows your DB to leverage the index over id to directly jump to the first relevant row instead of going through all 5000753 rows preceeding it.