CODEWITHSUNDEEP
mysqlquery-optimizationcodeigniter-3
Mukhila Asokan
Mukhila Asokan

Reputation: 653

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

Upvotes: 0

Views: 184

Answers (2)

Rick James
Rick James

Reputation: 142528

Do you at least have INDEX(year)? And an index (or PRIMARY KEY) on estimationid in each table?

Do you need all the columns (SELECT *)? This can be especially costly if any are TEXT or BLOB.

Are you trying to fetch a million (10 lakh) rows? The network may be choking.

PHP has a memory limit that may be choking on that much data being thrown at it all at once.

Pagination is quite costly if you first fetch all the rows.

Pagination is also costly if you use LIMIT and OFFSET.

Pagination can be efficient if you "remember where you left off". See this.

Upvotes: 0

Mihir Bhende
Mihir Bhende

Reputation: 9045

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.

Upvotes: 0

Related Questions