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

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

    Pagination exteremly large tables with phpmysql


    Hi;

    I usually use simple pagination methods using offset limit for small tables.

    I am not going to go very deep in to the details but I am having a hard time finding solution for high performance method for paginating large tables.

    In general how do YOU build pagination for exteremly large tables where matching results could be +500.000 (hardware factory with very many products)? It looks like there is no way to deal with large offset limit.

    In this over simplified example id is Primary key but still takes 20 seconds:
    Code:
    EXPLAIN SELECT id
    FROM `products_table`
    LIMIT 200000 , 10
  2. #2
  3. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    Not sure what kind of hardware you're using there, but I deal with tables in the millions of rows and it would never take more than a moment to pull out those records. Either something is really wrong, you have a faulty premise (it's not really ID'd correctly), or you just need to upgrade your hardware. If you can't upgrade, you might want to start thinking about sharding tables.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  4. #3
  5. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Infinite scrolling:

    http://docs.sencha.com/extjs/4.2.0/e...te-scroll.html

    The "pagination" can be done behind the scenes server side chunked into appropriate sizes for the size of your dataset and available hardware.

    As a complete aside, what happens if you take one of your long running queries and put it into phpmyadmin? does it still take as long? what about with and without pagination?

    Then, put an "EXPLAIN" before the query and execute that, that may show you some bottlenecks.

    The thing is, i don't think that your described dataset should be giving you the problems are talking about.

    Other things to check:

    are indexes added on all fields on which you may search or join on?

    are your tables myisam or innodb? (myisam is super fast for reading while innodb gives advanced database deatures more flexibility)

    There's also the mysql performance blog
    Last edited by Northie; May 13th, 2013 at 05:39 AM.
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,671
    Rep Power
    171
    Hi friends;

    I believe this is a bit more complex than what it looks like

    There is a lot of Mystery about Mysql and InnoDB and unlimited things to test to be honest I only see r397 as the only one who knows EXACTLY how things work around databases!

    I found a post of r397 in another forum (I believe it is called SitePoint) from years back, and tried his solution, see what happened:
    Code:
    SELECT id FROM  `products_table`  LIMIT 200000 , 10
    
    Showing rows 0 - 9 (~101 total, Query took 266.0895 sec)
    r397's suggestion:
    Code:
    SELECT id FROM  `products_table`  ORDER BY id LIMIT 200000 , 10
    
    Showing rows 0 - 9 (~101 total, Query took 0.2475 sec)
    Saying that, I am really curious to see what his explaination would be here. He surprises me sometimes.

    When it comes to any OOP realted issues Northie is by far the best in Devshed.

    Anyways, thanks.
    -----------
    EDIT:
    Northie, infinite scrolling looks great, only if there was no JS involved.
    Last edited by zxcvbnm; May 13th, 2013 at 05:59 AM.
  8. #5
  9. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Originally Posted by zxcvbnm
    When it comes to any OOP realted issues Northie is by far the best in Devshed.
    er, no - there are far more knowledgeable ones than I wrt OOP - but I'm glad you think I know enough to help, thanks for the compliment!

    EDIT

    infinite scrolling looks great, only if there was no JS involved.
    Javascript is one of your best friends - try thinking about the concept of splitting your application logic between server side and client side, all of a sudden some very interesting ideas and possibilities present themselves....and while we're on the subject of breaking conventions, will you abandon the page metaphor of "web pages" and start to think about "viewports" to data and application services instead? - food for thought!
    Last edited by Northie; May 13th, 2013 at 06:12 AM.
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Saying that, I am really curious to see what his explaination would be here. He surprises me sometimes.
    no idea, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo