douyun3631
douyun3631
2013-11-28 11:30

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

已采纳

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
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongmu7335 dongmu7335 8年前

    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.

    点赞 评论 复制链接分享
提交

为你推荐