homepage Welcome to WebmasterWorld Guest from 54.161.214.221
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
How to do faster pagination in php/mysql
8kobe




msg:3543714
 3:31 am on Jan 9, 2008 (gmt 0)

Currently I am doing the whole limit 0, 30 deal. This works great for about my first 20 pages of results. However it is very slow for pages 100+ . I understand that the issue is that it has to go through 3000 records to get my next thirty thus it would slow down. How can I update the table structure to speed it up.

 

lammert




msg:3544720
 9:13 am on Jan 10, 2008 (gmt 0)

An index over your table(s) that exactly matches your query can speed up things a lot, but at the end MySQL has to step through all the records until it reaches number 3001..3030. This causes the time to increase linearly with the pagenumber people are viewing.

If the data doesn't change often, you might consider to dump the result of the query in a temporary table and give each record in that table a unique sequence number starting at 1. With a PRIMARY index on that sequence number, you can jump directly to the wanted records with the WHERE clause in a SELECT statement.

For example for page 101, you can request the records with ID number 3001..3030 from your temporary table which will be equally fast as requesting the records 1..30 for page 1.

8kobe




msg:3544936
 3:04 pm on Jan 10, 2008 (gmt 0)

Lammert,

That sounds like what I want to do exactly. I kind of thought that would be the way to go, however I am not sure on how to implement it. The table will never change without me changing it. So I could make it so that after I update the table I update the temporary table. I haven't really ever dealt with Temporary tables (as I have never needed to in a couple years of web developing). Can you give me a basic example of how to do this in pseudo code? I basically would like to see how to make the temporary table and how to update the table without disabling the query when I make changes to it.

Thanks

8kobe




msg:3544958
 3:26 pm on Jan 10, 2008 (gmt 0)

I was just reading about temporary tables. It looks like they are dropped as soon as that page is done. If that is so i don't think it would speed it up. Would a similar thing with a non-temporary table get teh same results?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved