如何加快数据库进程？ [英] How can I speed up the database process?

问题描述

我有一个MySQL数据库，有超过9000行有10个字段。它是从Excel文件导入的，因此它都在一个表中。

当我运行查询，已经缩小了选择，我得到7000行回到我的Codeigniter视图页面。浏览器冻结30秒，并要求我杀死页面，因为没有反应。如果我等待，我会看到结果最终显示。

解决方案

你是否在一个循环中运行你的查询？ / p>

同意分页答案，使用限制和偏移。如果你运行10per页面700的查询。我将使用codeigniter的分页库如下。

  $ route ['controller /（：num）'] ='controller / index / $ 1'; 
  

-

  public function index（$ offset = 0）
 {
 
 //每个结果设置一个限制值10 
 $ limit = 10; 
 
 //查询数据库
 $ q =SELECT * FROM {table_name} LIMIT = {limit} OFFSET = {offset} ORDER BY {date} desc 
 
 //计算结果
 $ count = count（{query results}）; 
 
 // setup pagination config 
 $ config = array（
'base_url'=> site_url（'controller /'），
'total_rows'=> $ count，
'per_page'=> $ limit，
'uri_segment'=> 2 
）; 
 
 //初始化pagigination 
 $ this-> pagination-> initialize（$ config）; 
 
 //加载视图和分页数据
 $ this-> load-> view（'link_to_template'，array（
'pagination'=> $ this- > pagination-> create_links（），
'results'=> {query results} 
））; 
 
} 
  

I have a MySQL database that has over 9000 rows with 10 fields. It was imported from an Excel file so it's all in one table.

When I run the query, which has already narrowed down the selection, I get 7000 rows back to my Codeigniter view page. The browser freezes for 30 seconds and asks me to kill the page because there has been no response. If I wait, I will see the result show up eventually. I was wondering if there are any ways to improve this?

解决方案

Are you running your queries inside a loop of some sort?

Agree with pagination answers, use limits and offsets. If you run 10per page thats 700 queries. I would use codeigniter's pagination lib as follows.

$route['controller/(:num)'] = 'controller/index/$1';

-

public function index($offset=0)
{

   //set a limit of 10 per result
   $limit = 10;

   //query the database
   $q = "SELECT * FROM {table_name} LIMIT={limit} OFFSET={offset} ORDER BY {date} desc";

   //count the results
   $count = count({query results});

   //setup pagination config
   $config = array(
        'base_url' => site_url('controller/'),
        'total_rows' => $count,
        'per_page' => $limit,
        'uri_segment' => 2
   );

   //init the pagigination
   $this->pagination->initialize($config);

   //load the view and pagination data
    $this->load->view('link_to_template', array(
            'pagination'  =>  $this->pagination->create_links(),
            'results'  =>  {query results}
    ));

} 

这篇关于如何加快数据库进程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！