Search code examples
mysqlquery-optimizationcodeigniter-3

Program not responsive when fetching data from database using mysql

In my mysql database, i have more than 1 million rows in the row. Using Join query i fetched the database but it take more time to retrieve the data and also take time to display the data using foreach loop in php. It shows error like program not responsive.

SQl query is execute and it take more time. Display the data in codeigniter and used jquery pagination, it also take more time to display.

I used Codeigniter pagination now it fast. But it shows error in SQL. Please find this

Tables

Table name - catch_estimation
estiamteid int(11) PRI
centerid int(11)
centername varchar(100)
month int(2)
year int(5)
totalcatch int(11)
totalefforts int(11)
totalafh int(11)
noofgears int(11)
created_date datetime
modified_date datetime

Table name gearwise_estimation

id int(11) NO PRI
estimationid int(11)
centername varchar(50)
gearname varchar(20)
gear_totalefforts int(11)
gear_totalafh int(11)
gear_totalcatch int(11)

     $this->db->select('start.*');
    $this->db->from('gearwise_estimation');
    $this->db->join('catch_estimation', 'gearwise_estimation.estimationid = catch_estimation.estiamteid and year = '.$year);

   foreach($month as $mon):
        $this->db->like('catch_estimation.month',trim($mon));
    endforeach;

     foreach($gear as $gr):
        $this->db->like('gearwise_estimation.gearname',trim($gr));
    endforeach; 

   foreach($centername as $zo):
        $this->db->like('catch_estimation.centerid',trim($zo));
    endforeach;

    $this->db->order_by("catch_estimation.month", "asc");   
    $this->db->order_by("catch_estimation.centername", "asc");

But it shows error like 

SELECT `start`.* FROM `gearwise_estimation` JOIN `catch_estimation` ON `gearwise_estimation`.`estimationid` = `catch_estimation`.`estiamteid` and `year` = 2018 WHERE catch_estimation.month LIKE '%8%' ESCAPE '!' AND catch_estimation.month LIKE '%9%' ESCAPE '!' AND gearwise_estimation.gearname LIKE '%OBGN%' ESCAPE '!' AND catch_estimation.centerid LIKE '%2%' ESCAPE '!' ORDER BY `catch_estimation`.`month` ASC, `catch_estimation`.`centername` ASC
Solution

  • If you have 10 lakhs+ rows in the table, then it is not feasible to have DOM level pagination. Currently your pagination is getting all 10 lakh rows from the database and then doing hide/show on DOM level to form pagination.

    You should do apply limit clause in mysql and then only fetch the records you need per page.

    See this for your reference.

    Once you are showing only the data you need, then you can go ahead and optimise more via adding indexes, using Explain to get query execution details.