我对CI比较陌生,我读到使用join可能会减慢报告速度,在我的数据库中将有超过100k条记录。我已经在 CI 中编写了这两个查询,但没有注意到太大区别。我想知道我是否正确地编写了这些查询,或者是否有更好的方法来获取此类查询,不幸的是,我不知道如何将这些内容发布在类似小提琴的示例结构中。
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;
}
这是第二个函数
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;
}
我想知道是否有更优化的方法来编写相同的查询,因为此时这两个函数都需要大约 6 秒才能显示。结果稍后可能会增长到 200k 以上,显示它的时间可能会增加。
任何帮助将不胜感激。
return $this->db->query("your query here");
您是否尝试过上面的查询,并使用 * 选择