我的数据库中有100万条记录。 如何更快地分页..？

Question

I have 1 million+ records in database.

On the index page, multiple filter to filter through database create where clause. eg

select * from primaryinfo where category='abc' and technology='PQR'

I want to show--: 1. number of records found 2. pages. 3. 10 out 100(kind of thing) on the page.

Am sending the filtered records as json object to jquery, am looping through the records and appending to a particular div.

below is my php pagination code

$selectQ = "select * from primaryinfo where  match(title,description,tags)       against('".$searchCombine."') and category='abc' and technology='pqr'";
$result = mysql_query($selectQ);
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $per_page);
$start;
$end;
if (isset($_POST['pagecc']))
{
$show_page = $_POST['pagecc'];  
if ($show_page > 0 && $show_page <= $total_pages)
{
    $start = ($show_page - 1) * $per_page;
    $end = $start + $per_page;
} else
{ 
        $start = 0;              
    $end = $per_page;
}
 }
else
{ 
$start = 0;
$end = $per_page;
} 
if($end > $total_results)
    $end = $total_results; 
for($i=$start;$i<$end;$i++){
// here the json object is created
}

Answer 1

First you can get the total:

select COUNT(*) from primaryinfo 
where  match(title,description,tags) against('searchCombine') 
       and category='abc' 
       and technology='pqr' 

Then you can paginate using the LIMIT feature:

select * from primaryinfo 
where  match(title,description,tags) against('searchCombine') 
       and category='abc' 
       and technology='pqr' 
LIMIT 0 10; -- Start at offset 0 show ten items per page

Please note that the mysql_* functions are deprecated and will be removed in a future PHP version. Please consider using mysqli or PDO.

To further improve performance you could look at setting up indexes on columns. Particularly the category and technology columns but this will depend on your data.