I am relatively new to CI, I read that using join may slow down reporting, In my database there will be more than 100k records. I have written these two queries in CI but havent noticed much difference. I want to know if I am writing these queries appropriately or if there is a better way to get such query, unfortunately I do not know how to post these in a sample fiddle like structure.
function select_data_specific_customer($from, $to, $customer, $threshold = '') {
//die($from);
$currentuserid = $this->session->userdata('userid');
$this->db->select('timestamp');
$this->db->select('count');
$this->db->select('price');
$this->db->select('totalPrice');
$this->db->select('reference');
$this->db->select('customers_r');
$this->db->select('userID_r');
$this->db->from('logger');
$this->db->from('users_customer');
$this->db->where(array('users_customer.custo_id' => "logger.customers"), NULL, FALSE);
//users_customer contains connection between users and customers ( id userID custo_id checked )
$this->db->where(array('users_customer.userID' => $currentuserid), NULL, FALSE);
$this->db->where(array('users_customer.checked' => '1'), NULL, FALSE);
$this->db->where('cast(timestamp as date) BETWEEN "' . date('Y-m-d', strtotime(str_replace('-', '/', $from))) . '" and "' . date('Y-m-d', strtotime(str_replace('-', '/', $to))) . '"');
$query = $this->db->get();
if ($query->num_rows() > 0) {
foreach ($query->result() as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
This is the second function
function select_data_specific_customer($from, $to, $customer, $threshold = '') {
//die($from);
$currentuserid = $this->session->userdata('userid');
$this->db->select('timestamp');
$this->db->select('count');
$this->db->select('price');
$this->db->select('totalPrice');
$this->db->select('reference');
$this->db->select('customers_r');
$this->db->select('userID_r');
$this->db->from('logger');
$this->db->join("users_customer", "users_customer.custo_id=logger.customers", 'inner');
$this->db->where(array('users_customer.userID' => $currentuserid), NULL, FALSE);
$this->db->where(array('users_customer.checked' => '1'), NULL, FALSE);
$this->db->where('cast(timestamp as date) BETWEEN "' . date('Y-m-d', strtotime(str_replace('-', '/', $from))) . '" and "' . date('Y-m-d', strtotime(str_replace('-', '/', $to))) . '"');
$query = $this->db->get();
if ($query->num_rows() > 0) {
foreach ($query->result() as $row) {
$data[] = $row;
}
return $data;
}
return false;
}
I wish to know if there is a more optimal way to write the same query, because at this moment both function is taking about 6 second to display. The result can grow to more than 200k later and the time might increase to display it.
Any help will be appreciated.
did you try query above, and use * to select