使数据表加载速度更快，数据量超过10000

Question

我是数据表的新手，我正在使用数据表进行网站搜索数据。但是mysql的数据超过了10000。当我尝试在网上搜索数据时，数据表显示的数据非常长。有人能帮我吗？我如何让数据表更快地显示大数据的表。谢谢

PHP:

<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "test");
$columns = array('id', 'datetime', 'temperature', 'humidity');

$query = "SELECT id, datetime, temperature, humidity FROM data WHERE ";
if($_POST["is_date_search"] == "yes")
{
$query .= 'DATE(datetime) BETWEEN "'.$_POST["start_date"].'" AND  "'.$_POST["end_date"].'" AND ';
}
if(isset($_POST["search"]["value"]))
{
$query .= '
(id LIKE "%'.$_POST["search"]["value"].'%")
';
}
if(isset($_POST["order"]))
{
$query .= "GROUP BY DATE_FORMAT(datetime, '%d-%M-%Y-%H:%i:%s') ORDER BY 'id'";
}


$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query );
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = ""; 
$sub_array[] = $row["datetime"];
$sub_array[] = $row["temperature"];
$sub_array[] = $row["humidity"];
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM data";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw"    => intval($_POST["draw"]),
"recordsTotal"  =>  get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data"    => $data
);
echo json_encode($output);
?>

Javascript：

$(document).ready(function(){
$('.input-daterange').datepicker({
todayBtn:'linked',
format: "yyyy-mm-dd",
autoclose: true
});
fetch_data('no');
function fetch_data(is_date_search, start_date='', end_date='')
{
var dataTable = $('#tabel_data').DataTable({
"columnDefs": [ {
"searchable": false,
"orderable": false,
"targets": 0
} ],
"order": [[ 1, 'asc' ]],
dom: 'Bfrtip',
buttons: [
{
extend: 'print',
filename: 'datatable'
},
],
"paging": false,
"processing" : true,
"serverSide" : true,
bFilter:false,
"ajax" : {
url:"fetch.php",
type:"POST",
data:{
is_date_search:is_date_search, start_date:start_date, end_date:end_date
}, 
}
});
dataTable.on('draw.dt', function () {
var info = dataTable.page.info();
dataTable.column(0, { search: 'applied', order: 'applied', page: 'applied', }).nodes().each(function (cell, i) {
cell.innerHTML = i + 1 + info.start;
dataTable.cell(cell).invalidate('dom'); 
});
}); 

}

$('#search').click(function(){
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();
if(start_date != '' && end_date !='')
{
$('#tabel_data').DataTable().destroy();
fetch_data('yes', start_date, end_date);
document.getElementById('tabel').style.display = "block";  
}
else
{
alert("Date Required");
}
}); 

});

Answer 1

1(使用分页如何在PHP和mysql中使用分页，而不是像下面这样的简单查询。

"SELECT * FROM data";

2( 点select *，因为*会选择不必要的字段，而是定义要显示数据的列名

示例：

select name,page,amt from data 

您的最终查询将看起来像

SELECT emp_id, emp_name, emp_salary FROM employee LIMIT $offset, $rec_limit;

查看上面的链接如何使用PHP和mysql 进行分页