Paging through thousands of records

[CaptainStarbuck]

Consider 10,000 images in a directory, like icons, avatars, or someone's recent vacation pictures. Put these all in a MySQL table where each row represents one file, with columns for a filename, description, and some other data. We have code that shows these images in pages but it takes about 1 full minute to move from one page to the next. It seems that for each request the code sorts the database, indexes to the desired row, then builds the current page as a string. I'm looking for much more efficient code that might pre-page the data on the server (assume all pages are the same size) and then have every request just index into the result set at the right position and build the display code more effectively - a more random access approach than sequential.

 

Can someone point me to code that does this, in a package that handles this well?

 

Might this be best done by creating a separate index table whenever the files are updated, where the primary key is the page number and columns for image1, image2, etc are the keys to the images that appear on that page?

 

I know this seems like a problem that would have been solved a decade ago, but algorithms evolve...

 

Thanks!!!

[rhodesa]

10,000 records it chump change for the database. can you post the code that generates the page?

[Maq]

Yeah, 10k amount of records is nothing for a database.

 

We have code that shows these images in pages but it takes about 1 full minute to move from one page to the next.

 

Please post the code for this problem so we can fix it 

[PFMaBiSmAd]

I'm going to guess that the actual page generation time by the server is minimal (less than one second), but that you are using full size images in the content and that the full size images must be fetched by the browser on each page load.

 

Have you actually determined what the page generating time is to isolate if the long page load times are due to the database accesses or due to the time it takes the content to be transfered?