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;


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?

  • Why do you need such a high offset? Can't you use some other way to rewrite? Like WHERE log_time >= @time_value ORDER BY log_time LIMIT 50; ? – ypercubeᵀᴹ Mar 2 '15 at 15:11
  • sounds good, But I use limit because of the pagination. – BlueBird Mar 2 '15 at 15:13
  • And someone read 100 thousand pages? (that's 5000753 divided by 50) Or you provide links for the 1st, 2nd, 3rd, ..., 1-millionth page? – ypercubeᵀᴹ Mar 2 '15 at 15:15
  • actually, it is an security audit. User should be able to check any location of the table. viewing page 1 by 1 is not practical for such a huge data. So it should be possible to jump to any location by page numbers. – BlueBird Mar 2 '15 at 15:21
  • 1
    It's really hard then. I suggest you explain that using intervals of the log_time (instead of page numbers) would improve performance. Say, you split to intervals of 1 minute (or 10 seconds, whatever). The pages will not have stable number of rows (50) but variable. But it wil lbe much more efficient. – ypercubeᵀᴹ Mar 2 '15 at 15:26

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:

FROM securityData 
WHERE id > ?last_seen_id
ORDER BY log_time 

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.

  • Totally correct. There's no other option. But you won't be able to jump to an arbitrary page number which is what BlueBird wants to achieve faster. – Tom Desp Mar 29 '16 at 7:31
  • This assumes that ORDER BY log_time corresponds precisely with ORDER BY id, which may not necessarily be the case. – Andriy M Mar 29 '16 at 9:51
  • More discussion of not using OFFSET. – Rick James Nov 22 '18 at 19:30

There are two(2) features of this query that makes it struggle:

  • SELECT *

There has to be a temp table that needs to created, loaded, and sorted with all the columns from the securityData table. Doing pagination should not involve all columns from a source table in the very beginning of the query.

What is needed ? Separation of keys for pagination from the gathering of all columns.

STEP 01 : Gather keys for pagination

SELECT id FROM securityData ORDER BY log_time LIMIT 5000753, 50;

STEP 02 : Make that pagination query a subquery

STEP 03 : Join pagination to the securityData table

(SELECT id FROM securityData ORDER BY log_time LIMIT 5000753, 50) A
INNER JOIN securityData B USING (id)
ORDER BY B.log_time;

The main idea is to gather just 50 keys into a temp and join back to source table.

This can be very performant regardless what any EXPLAIN plan says. I can say this because almost 6 years ago, I addressed this in a similar post in StackOverflow entitled "Fetching a Single Row from Join Table". I answered it and got nice bounty just by gathering pagination keys first.

My inspiration for rewriting the query this way came from a French Performance Consultant's YouTube Video. Learn form this video and you will never look at pagination the same.

If you decide to paginate this way, please keep any optimizations localized to the subquery. The main point is to collect the keys. Increasing the performance of gathering these keys will increase the whole query's performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.