Optimized solution for pagination

I'm making a pagination for a very large database. So it is important to use best query. Now I need to know what is the fastest way to know mysql rows number? which one is the best an开发者_如何学Cd why?

You need to use Limit in MySQL: http://www.java2s.com/Code/SQL/Select-Clause/UseLIMITinSELECT.htm

It's the best because is the only way to do "pagination" on the database side.

$perPage = 20;
$page = x;

$content = get_page(x);

function get_page($p)
{
    $query = "SELECT * FROM `content` LIMIT ".($p-1)*$perPage.",".$perPage;
    ...
}

You can get the total number of rows that will be returned by your query:

SELECT COUNT(*) FROM your query here;

Then to paginate your query with OFFSET(first row to start at) and LIMIT(maximum number of rows to return):

SELECT your query LIMIT 10 OFFSET 10;

I've collected a number of pagination plugins available in the web. It comes with a great user interactivity which enhances the user experience. They all use jquery and AJAX to implement the feature. http://itswadesh.wordpress.com/2012/04/19/11-jquery-pagination-plugins-you-must-try/