Question 417215 in Mysql Question posted in
The official MySQL documentation can be found here.

How to improve a pagination query in mysql

How to improve my query where I have used offset in mysql. I am trying to make pagination.

I have a database there I have more then 800k data for a single column.

Example:
My table data like:

-----------------------------------
|id   | ip_to   | ip_from  | country |

When I run a query like

SELECT * FROM ipdetails WHERE cournty= 'US' LIMIT 1000 OFFSET 800000;

It takes more then 20 second.
I have already create index with country

How can I solve this in this problem. I want to make it fast

You can test it lively.

https://ip.team71.link/public/find-by-country-code?countryCode=US&page=830

Tags:

2

Answers


  1. 0

    Assuming id is the primary of the table, it would be more efficient if you can create composite index as INDEX(country, id) instead which you can then add ORDER BY id to your query so that the result is more predictable and faster to execute

    SELECT * FROM ipdetails WHERE cournty= 'US' ORDER BY id LIMIT 1000 OFFSET 800000 ;
    Login or Signup to reply.
  2. 0

    If id is the primary key, then try:

    SELECT i.*
FROM ( SELECT id FROM ipdetails WHERE cournty= 'US' LIMIT 1000 OFFSET 800000 ) x,
     ipdetails i
WHERE i.id = x.id;

    It is faster for high offset values, because it can store more records in the server cache and the temporary table is much smaller. I use this kind of select for example with a table with 25000 records and 50 columns. If the offset is about 20000, the execution is about 2.5x faster.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search