Codeigniter活动记录查询花费太多时间从数据库加载数据 [英] Codeigniter active records query taking too much time to load data from database
本文介绍了Codeigniter活动记录查询花费太多时间从数据库加载数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用codeigniter
I'm using codeigniter
这是我的模型
<?php
class groups_Model extends CI_Model {
public function __construct(){
parent::__construct();
}
/**
* get All groups
* @return array object list of groups
*/
public function get(){
$this->db->select('groups.id,groups.name as group,status.name as status');
$this->db->from('groups');
$this->db->join('status', 'status.id = groups.status');
$this->db->order_by('groups.id', 'ASC');
$this->db->limit($this->config->item('per_page'),$this->uri->segment(4));
$query = $this->db->get();
$total = $query->num_rows();
if($total > 0){
return $query->result();
}
return false;
}
?>
现在我的数据库中有500000个虚拟记录
我正在抓取20记录使用分页
,但我的查询需要5到6秒
如何加快数据库性能
Now i have 500000 dummy records in my database
I'm fetching 20 records using pagination
but my query take 5 to 6 seconds
how to speed up database performance
控制器代码看起来像这样
controller code look like this
<?php
public function groups(){
$this->output->enable_profiler(TRUE);
$this->benchmark->mark('code_start');
$this->output->cache(5);
$this->load->model('groups_Model');
$this->load->library('pagination');
$this->config->load('pagination');
$config['base_url'] = base_url().'admin/auth/groups';
$config['total_rows'] =$this->groups_Model->count(null);
$this->pagination->initialize($config);
$data['groupsList']=$this->groups_Model->get();
$this->load->view('admin/templates/header');
$this->load->view('admin/groups',$data);
$this->load->view('admin/templates/footer');
$this->benchmark->mark('code_end');
echo $this->benchmark->elapsed_time('code_start', 'code_end');
}
?>
所有需要花费这么多时间运行的查询
All queries taking this much time to run
DATABASE: database_name (Auth:$db) QUERIES: 5 (5.8955 seconds) (Hide)
1.6279 SELECT * FROM groups
4.2598 SELECT `groups`.`id`, `groups`.`name` as `group`, `status`.`name` as `status`
FROM `groups`
JOIN `status` ON `status`.`id` = `groups`.`status`
ORDER BY `groups`.`id` ASC
LIMIT 40, 20
0.0078 SELECT `username`
FROM `users`
WHERE `id` = '1'
0.0000 SELECT `first_name`
FROM `users`
WHERE `id` = '1'
0.0000 SELECT `last_name`
FROM `users`
WHERE `id` = '1'
推荐答案
使用分页这是最好的使用方法
Use pagination this is the best way to use it
在模型中创建这些函数
模型
public function get_count($table){
return $this->db->count_all_results($table);
}
public function get_all_userdata($table, $where, $limit, $start){
$query = $this->db->get_where($table, $where, $limit, $start);
$data = $query->result_array();
return $data;
}
控制器
$where = array('status' => 0);
//pagination
$config['base_url'] = base_url('nonactiveusers');
$config['total_rows'] = $this->User_model->get_count();
$config['per_page'] = 5;
$config["num_links"] = 3;
$config['uri_segment'] = 2;
$config['full_tag_open'] = "<ul class='pagination'>";
$config['full_tag_close'] ="</ul>";
$config['num_tag_open'] = '<li>';
$config['num_tag_close'] = '</li>';
$config['cur_tag_open'] = "<li class='disabled'><li class='active'><a href='#'>";
$config['cur_tag_close'] = "<span class='sr-only'></span></a></li>";
$config['next_tag_open'] = "<li>";
$config['next_tag_close'] = "</li>";
$config['prev_tag_open'] = "<li>";
$config['prev_tag_close'] = "</li>";
$config['first_tag_open'] = "<li>";
$config['first_tag_close'] = "</li>";
$config['last_tag_open'] = "<li>";
$config['last_tag_close'] = "</li>";
$config['first_link'] = "<<";
$config['last_link'] = ">>";
$this->pagination->initialize($config);
$page = $this->uri->segment(3); // your uri segment here
$data['links'] = $this->pagination->create_links();
$result = $this->User_model->get_all_userdata("users", $where, $config['per_page'], $page);
$data['users'] = $result;
$this->load->view('view', $data);
这篇关于Codeigniter活动记录查询花费太多时间从数据库加载数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文