#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171

    How to deal with sloq sql large offset limit


    Hello;

    In this over simplified example id is Primary key but still takes 20 seconds to show 10 results.
    Code:
    SELECT id
           FROM `products_table`
           LIMIT 200000 , 10
    What is the solution in such situations to improve the performance?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    looks like pagination

    i wanna meet your user who patiently hits the "next 10" button twenty thousand times

    the solution, of course, is to ignore this problem for unreasonably large offsets
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171
    Originally Posted by r937
    looks like pagination

    i wanna meet your user who patiently hits the "next 10" button twenty thousand times

    the solution, of course, is to ignore this problem for unreasonably large offsets
    Hi r937
    I was almost sure that is the answer I am going to get. I found different answers to this question in Google but none of them convinced me.

    Would you please tell me what is the right way to deal with this situation? In general how would you group (orgonize) results for exteremly large tables where matching results could be +500.000? For example a hardware factory with very many products.

    Thank you
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    half a million search results? you mean, like a typical google query?

    be honest, now, how often have you gone past page 20?

    i understand that pagination gets slower the deeper you go into the results, but why is this a problem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171
    Originally Posted by r937
    half a million search results? you mean, like a typical google query?

    be honest, now, how often have you gone past page 20?
    I never pass page 3 in Google.

    This is my uncles Wood and Hardware production/import/export company who have everything on local ap already; he is old school. He can't be bothered as the previous wbsite was WAY TOO SLOW and did cost him a fortune. I offered him to build the system on line so they could access from anywhere. He is in LA, sales people are in China, clients are in Dubai.

    I am wondering about this because I have never dealt with such big database and I really want to learn the proper way of dealing with real life limitations, what opportunity better than this?

    It also happened to be exact time where I picked up High Performance Mysql.
    Originally Posted by r937
    i understand that pagination gets slower the deeper you go into the results, but why is this a problem?
    Really? That is how it's supposed to be? Then wow.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo