how to reduce speed of datatable

Here is my code i am using jquey datatable i am fetching records from 1 table only but it's take hardly 5 sec to load records when i am trying to search records at that time at that time it's take 14 sec how to reduce data fetching sec i am already use searchDelay: 10 for reduce speed but still i am facing same problem.

Controller :

function customer_is_com_wise_datatable(){
$post_data = $this->input->post();
$id = $this->session->userdata('teen_is_logged_in')['customer_id'];
$query = $this->app_model->get_categories_customer_list($id);
$parent_data = $this->array_flatten($query,null);
$customer_id_arr = array();
if(!empty($parent_data)){
foreach ($parent_data as $parent){
$customer_id_arr = $parent['customer_id'];
}
}
$config['table'] = 'customers c';
$config['select'] = 'c.*,c1.name as parent_name';
$config['joins'] = array('join_table' => 'customers c1', 'join_by' => 'c1.customer_id = c.parent_id', 'join_type' => 'left');
$config['column_order'] = array(null, 'c.name','c.phone1','c.phone2','c.city','c.current_balance','c1.name');
$config['column_search'] = array('c.name','c.phone1','c.phone2','c.city','c.current_balance','c1.name','c.login_type');
$config['order'] = array('c.customer_id' => 'desc');
if(!empty($customer_id_arr)){
$customer_ids = implode(',', $customer_id_arr);
$config['custom_where'] = 'c.customer_id IN('.$customer_ids.')';
}
if($post_data['is_computer'] == 2){ } else {
$config['wheres'] = array('column_name' => 'c.is_computer', 'column_value' => $post_data['is_computer']);
}
$login_types = array();
if($post_data['main_dealer'] == 'true'){
$login_types = LOGIN_TYPE_MAIN_DEALER;
}
if($post_data['dealer'] == 'true'){
$login_types = LOGIN_TYPE_DEALER;
}
if($post_data['customer'] == 'true'){
$login_types = LOGIN_TYPE_CUSTOMER;
}
if(!empty($login_types)){
$login_type = implode(',', $login_types);
$config['custom_where'] = 'c.login_type IN('.$login_type.')';
}
$this->load->library('datatables', $config, 'datatable');
$list = $this->datatable->get_datatables();
$data = array();
// echo '<pre>'; print_r($list); exit;
foreach ($list as $customer) {
$row = array();

$action = '';
$action .= '<a href="' . base_url("customer/add/" . $customer->customer_id) . '"><span class="edit_button glyphicon glyphicon-edit data-href="#"" style="color : #419bf4" >&nbsp;</span></a>';
//$action .= '<a href="" customer_id="' . $customer->customer_id . '" title="Click to Edit"><span class="glyphicon glyphicon-edit" style="color : #419bf4">&nbsp;</span></a>';
$action .= '<a href="javascript:void(0);" class="delete_button" data-href="' . base_url('customer/delete_customer/'.$customer->customer_id) . '" title="Click to Delete"><span class="glyphicon glyphicon-trash" style="color : red">&nbsp;</span></a>';
if($customer->active_deactive == 1){
$action .= '<a href="javascript:void(0);" class="deactive_btn" data-href="' . base_url('customer/active_deactive_customer/'.$customer->customer_id) . '" title="Click to Deactive" style="margin-right: 5px;"><span class="fa fa-check text-success">&nbsp;</span></a> ';
} else {
$action .= '<a href="javascript:void(0);" class="active_btn" data-href="' . base_url('customer/active_deactive_customer/'.$customer->customer_id) . '" title="Click to Active" style="margin-right: 8px;"><span class="fa fa-close text-danger">&nbsp;</span></a> ';
}
if($customer->private_room_allow == 1){
$action .= '<a href="javascript:void(0);" class="disallow_private_room_btn" data-href="' . base_url('customer/private_room_allow_customer/'.$customer->customer_id) . '" title="Click to Disallow Private Room"><span class="fa fa-home text-success">&nbsp;</span></a> ';
} else {
$action .= '<a href="javascript:void(0);" class="allow_private_room_btn" data-href="' . base_url('customer/private_room_allow_customer/'.$customer->customer_id) . '" title="Click to Allow Private Room"><span class="fa fa-home text-danger">&nbsp;</span></a> ';
}
$row = $action;
$row = $customer->name;
if($customer->login_type == '1'){
$customer->login_type = 'Admin';
} elseif ($customer->login_type == '2') {
$customer->login_type = 'Main Dealer';
} elseif ($customer->login_type == '3') {
$customer->login_type = 'Dealer';
} else {
if($customer->is_computer == '0'){
$customer->login_type = 'Customer';
} else {
$customer->login_type = 'Customer (Computer)';
}
}
$row = $customer->login_type;
$row = $customer->parent_name;
$row = $customer->cust_comm . "%";
$row = $customer->commission_amount;
$row = $customer->current_balance;
$row = $customer->phone1;
$row = $customer->phone2;
$row = $customer->city;
$data = $row;

}

$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->datatable->count_all(),
"recordsFiltered" => $this->datatable->count_filtered(),
"data" => $data,
);
echo json_encode($output);


View : Using datatable

$("#is_computer").select2();
var table = $('#customer_list').DataTable({
"processing": true,
"ordering": true,
"serverSide": true,
searchDelay: 10,
"length" : 10,
"order": [[1, 'desc']],
// "searching": true,
retrieve: true,
//"aaSorting": [[1, 'desc']],
"ajax": {
"url": "<?php echo site_url('customer/customer_is_com_wise_datatable') ?>",
"type": "POST",
"data": function(d){
d.is_computer = $("#is_computer").val();
d.main_dealer = $("#main_dealer").prop('checked');
d.dealer = $("#dealer").prop('checked');
d.customer = $("#customer").prop('checked');
}
},

"scrollY": 450,
"scrollX": true,
"scroller": {
"loadingIndicator": true
},
// "sScrollX": "100%",
"sScrollXInner": "110%",
stateSave: true,
"columnDefs": [{
"className": "dt-right",
"targets": [4,5,6],
"orderable": false
}]

});


the first thing you've to do is - print out your sql statement and take a look how much time it needs. $this->db->last_query(); is the keyword here...
– sintakonte
Jun 30 at 8:13

$this->db->last_query();

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.