Best way to display 60,000 records on a php webpage

I am looking to display 60,000 records on a webpage with php pulling the records from a mysql database on localhost. These 60,000 records may change depending on the data input.

The records have 5 text fields and due to the sheer number of records, a significant time is taken to send the data from the mysql server to the web browser. Even on a localhost, the time taking is around 15 seconds. During this time, the page is empty.

I would like to seek professional opinion on how to either to 1. display the data in an alternative method, (which I'm not sure what method) or 2. hasten the sending of data from mysql server to the web browser using caching technology like memcache.

In the end i will be deploying the application on the internet where the la开发者_运维百科g would be immensely unacceptable (i.e. > 15 seconds).

Thank you and Best Regards!

I would suggest trying AJAX pagination. No user will be able to see and analyze 60k records at one time. You can have the php display the first x (however many fit on the average screen or two) records to fill 2-3 pages, and have JavaScript listen for a scroll change. If a user starts scrolling down, have it automatically query the next y records, and add them to the display list. Possibly also removing the records from the top of the list.

Also, adding some quick-jump links or a search feature could help, as you wouldn't want to scroll down 60k records to make changes.

This will significantly lighten the server and client load, as it would only have to serve up a couple hundred records at a time.

DataTable

You should have a look at YUI's DataTable. You should hook the datatable up to autocomplete. There is also an example how they did it in YUI2(help) but YUI3 is a lot faster.

Caching

Caching is also important. You say you could use memcached so that is very good. I am a big fan of redis(But both will work, but the nice thing is that redis is I think better suited for autocomplete). There is even a free plan of Redis To go.

Another important tip is to make sure you are getting your data as you want it displayed from the database. In other words if there is any calculation or processing that you have to do, avoid doing it in PHP code during loops. Use SQL functions to process data, name fields, etc. Databases are good at that sort of thing. Of course this may or may not apply to exactly what you're doing.