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?
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).