Best way to sort MySql results based on two columns and show results using pagination?

Question

I want to sort results set first based on one column and then based on second column. I know how to do it on server side. And then I want to show these results with pagination feature.

Question: would it be better to do it on client side via ajax in jQuery? I'm using Zend Framework. Would Zend_Paginator module be useful in this scenario? I mean if unordered set is returned by server then using jquery to sort results based on any two columns would be better option, I think? How can I do that?

Basically I want to evaluate all the possible ways. Which one would be best and/or simplest option given I'm using jQuery and Zend Framework?

Answer 1

Sending large amounts of data from server to client is always bad idea

Answer 2

stumbled across this the other day which you might find useful: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

the http://www.mysqlperformanceblog.com/ is worth a read too :P

Answer 3

If you are going to send the whole result to client. You can use a Jquery plugin eg. Datatable. It can paginate and sort

otherwise to sort and paginate in mysql, the sql would look like:

SELECT * FROM data SORT BY first_column ASC, second_column DESC LIMIT 20, 10

LIMIT 20, 10, means , select 10 rows at offset of 20, which means, show 10 rows in page 3