CODEWITHSUNDEEP
phpcodeigniter
Gandalf
Gandalf

Reputation: 13693

Codeigniter too slow in paginating 2 million records

I have this model

<?php

class Cities_model extends CI_Model {

    protected $table = 'cities';

    public function __construct() {
        parent::__construct();
    }

    public function get_count() {
        return $this->db->count_all($this->table);
    }

    public function get_cities($limit, $start) {
        $this->db->limit($limit, $start);
        $query = $this->db->get($this->table);

        return $query->result();
    }
}

and this controller

<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Cities extends CI_Controller {

    public function __construct() {
        parent:: __construct();

        $this->load->helper('url');
        $this->load->model('cities_model');
        $this->load->library("pagination");
    }

    public function index() {
        $config = array();
        $config["base_url"] = base_url() . "cities";
        $config["total_rows"] = $this->cities_model->get_count();
        $config["per_page"] = 10;
        $config["uri_segment"] = 2;

        $this->pagination->initialize($config);

        $page = ($this->uri->segment(2)) ? $this->uri->segment(2) : 0;

        $data["links"] = $this->pagination->create_links();

        $data['cities'] = $this->cities_model->get_cities($config["per_page"], $page);

        $this->load->view('show_grid', $data);
    }
}

I have over two million records in mysql and the links are generated. However it takes 20 seconds to load.

I think get_count() is taking too long. Is there a way i can make the load times come down from 20 seconds?

Upvotes: 0

Views: 638

Answers (1)

Alex
Alex

Reputation: 9265

I would say the get_count() function is definitely causing you issues.

I would probably make a table to store the total. Then when something is added/deleted you simply add one or subtract one. Then you just get that number and it should happen almost instantly.

You could also consider caching the count or using the database cache (be warned, don't use the database cache unless you understand exactly how it works). Please note, these methods will also require some intervention on add/delete of records. In terms of the cache you would have to invalidate the cache variable. Database caching has a similar function: https://www.codeigniter.com/user_guide/database/caching.html#this-db-cache-delete-all

Upvotes: 1

Related Questions