Caching data from SQL database for sorting

Question

I got a MySql database that I do some potentially large queries to. The data I get from the query is stored in a 2D array in PHP which then generates a HTML table for the user to see. Currently I'm trying to implement a way to sort the data in various ways for the user. Of course I could just call the query again and tell it to sort in a different way, but I figure this would probably not be a very efficient way. My idea is to cache the data in some way and then call a javascript each time the data should be sorted in a new way to get the result. Unfortunately I don't know how to do that... :)

So, my questions are:
- Is it a good idea to do it that way?
- How could I implement this feature?

Answer 1

There are some javascript libraries available where you can use to sort the tables in the client side with out even making server calls. Its easy enough to sort on client side using javascript on your own. Here is one for your reference

Answer 2

How large do you expect this to scale? How much data is displayed in your HTML table at one time? If you expect that the number of rows in the table to reach the point where you have to paginate more than a handful of pages, it becomes more efficient to implement your sorts by re-calling the data from the database so you never have to fetch the entire dataset.

If you have 10,000 rows in your dataset, but only 30 on-screen at a time, the overhead of storing and sorting those rows on the client side isn't worth it.

If you know you will neverhave more than a handful of "pages" (say, less records than 5*number per page), then fetching it once and manipulating it on the client side is no big deal.

If you know that your workload will be 95% small and 5% massively large, then a hybrid might be in order: fetch, say, 150 records each time, and if you have less than 150, you have them all and it's safe to stay on the client side. If it's more than that, assume it could be massively large and fetch the data in chunks. And if there's more gradual slope of dataset sizes, you can always cache chunks and build up the dataset slowly as long as the sort order doesn't change.