Limit query can not be effective

Question

I have a question about a Mysql query.

I have some slow queries. Here is one of them:

SELECT  
    xxx_accounts.id 
     , xxx_accounts.name 
     , xxx_accounts.account_name 
     , xxx_accounts.address_postalcode 
     , xxx_accounts.address_city 
     , xxx_accounts.address_state  
     , xxx_accounts.date_modified 
     , xxx_accounts.assigned_user_id   
FROM 
    xxx_accounts  
WHERE
    xxx_accounts.deleted = 0 
ORDER BY 
    xxx_accounts.date_entered DESC 
LIMIT 4434950, 11;

It takes almost 2 minutes even if it use limit query.

Explain is here:

+----+-------------+--------------+------+--------------------------------------------------------------------------------+-------------------------+---------+-------+---------+-------------+                     
| id | select_type | table        | type | possible_keys                                                                  | key                     | key_len | ref   | rows    | Extra       |
+----+-------------+--------------+------+--------------------------------------------------------------------------------+-------------------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | xxx_accounts | ref  | idx_deleted_datemodified,idx_deleted_addresspostalcode,idx_deleted_dateentered | idx_deleted_dateentered | 2       | const | 1861322 | Using where |
+----+-------------+--------------+------+--------------------------------------------------------------------------------+-------------------------+---------+-------+---------+-------------+

This explain said that rows is total count.

However, this SQL is changed limit ""query,LIMIT 1,11"", it takes a few seconds (like 1 or 2 seconds).

The differnce of these SQL is between LIMIT 4434950,11 and LIMIT 1,11.

Can this problem be solved somehow?

How do you know that you're not interested in the first 4434950 rows when ordered by date? — Strawberry, Apr 11 '17 at 06:18
I'm sorry.I can not understand your question.please explain more detail. — shunsuke_stackoverflow, Apr 11 '17 at 06:35

score 2 · Accepted Answer · edited May 23 '17 at 12:10

Understand the difference between the two limits:

"LIMIT 1,11": means you are fetching only 1 record starting from 11 offset.

"LIMIT 4434950,11": means you are fetching 4434950 records starting from 11 offset. In this query table is scanned thousands row to get 4434950 records and then it stops, definitely it will take more time and resource as the result set is also having to many rows.

It's normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs.

The query cannot go right to OFFSET because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.

Some Trick to make this query fast:

MySQL cannot go directly to the 10000th record (or the 80000th byte as your suggesting) because it cannot assume that it's packed/ordered like that (or that it has continuous values in 1 to 10000). Although it might be that way in actuality, MySQL cannot assume that there are no holes/gaps/deleted ids.

So,

SELECT * FROM large ORDER BY id LIMIT 10000, 30

would be slow(er),

SELECT * FROM large WHERE id >  10000 ORDER BY id LIMIT 30

would be fast(er), and would return the same results provided that there are no missing ids (i.e. gaps).

Very good explanation here

It is possible by some means of query optimization. Check this link: http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down — Mayank Pandeyz, Apr 11 '17 at 06:17
Also you can use indexing based on xxx_accounts.deleted field — Abhisek Malakar, Apr 11 '17 at 06:27

Limit query can not be effective

1 Answers1