How can I speed up the database process?

Question

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?

+1 for "Over 9000". To the point: have you tried pagination? — Justin ᚅᚔᚈᚄᚒᚔ
If you're using an ORM of some kind, this will be really slow with that number of records. If you really need to list a large number of records - and you don't wish to paginate - then use a direct database call. — halfer
Jerry just as @Justin is pointing out you really need to consider paging through those results. Depending on the complexity of the HTML and any javascript involved rendering even 1000 to any browser is not a good idea and could do just like you are mentioning. So 7000 is definitely going to be a problem. Plus who in their right mind is going to scroll through 7000 rows. — spinon
@Justinᚅᚔᚈᚄᚒᚔ: what's the big deal with 9000 rows? — a_horse_with_no_name
@a_horse_with_no_name: 9000 rows isn't that remarkable. 9001, however... — Justin ᚅᚔᚈᚄᚒᚔ

Philip Philip · Accepted Answer · 2012-01-27T23:58:14

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

}

How can I speed up the database process?

4 Answers