CODEWITHSUNDEEP
mysqlcodeigniter
FlyingCat
FlyingCat

Reputation: 14270

How can I speed up the database process?

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?

Upvotes: 1

Views: 673

Answers (4)

Spec
Spec

Reputation: 329

Add indices (indexes) for particular columns; if columns are text, then add FULLTEXT indexes

Upvotes: 1

Philip
Philip

Reputation: 4592

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}
    ));

}

Upvotes: 1

Artur
Artur

Reputation: 7257

Start from reading about 'limit' in sql's selects to paginate your output.

Upvotes: 1

Matthew Flaschen
Matthew Flaschen

Reputation: 284927

Ideally, come up with a better query, or provide your users with better search terms. If they ultimately need to look through all 7000 rows, you need some kind of paging. One option is AJAX paging as explained in this article. Basically, you have a new request for each group of rows, but the user stays on the same page.

Upvotes: 1

Related Questions