Program not responsive when fetching data from database using mysql

Question

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

When you ask for help with query optimization, you should run `SHOW CREATE TABLE ` for each of the three tables used in your query, and include the result in your question. Also run `EXPLAIN SELECT * ...` to get the current query optimization plan for your query, and include that result. Please post these results as text, not screen shots. — Bill Karwin, Feb 12 '19 at 01:22
@BillKarwin its not for query optimization. sorry. Query is ok. To fetch the data it takes time. — Mukhila Asokan, Feb 12 '19 at 01:25
If something needs optimized, you need to be more specific. We don't know what is "more time". — James Z, Feb 12 '19 at 19:10
It still just says "more time" -- which could mean 5 minutes or 5 hours or something else and instead of error, you just have SQL code. — James Z, Feb 13 '19 at 17:26

score 0 · Accepted Answer · answered Feb 12 '19 at 01:24

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.

Thank you.First I will try in Codeingiter pagnition – Mukhila Asokan Feb 12 '19 at 01:33 — Mukhila Asokan, Feb 12 '19 at 01:33

score 0 · Answer 2 · answered Feb 13 '19 at 05:08

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.

Program not responsive when fetching data from database using mysql

2 Answers2