0

We have a large MySQL Table (say) 50,000 records or rows. The loading time of all the records is close to 20 mins. By loading I mean the data getting displayed in the browser or in a Javascript Grid - time taken to load this data is around 20 mins. This is creating a big problem for us. We have even tried to return all data in JSON following this approach How to convert result table to JSON array in MySQL still we are facing a prolonged delay in loading.

The query is very much straight forward as shown below. Can anyone help us with any solution to overcome this problem. Thanks in advance!

<?php
include 'includes/xxx.php';

$returnArray = array();
$totalSymptoms = 0;
$matchedSymptomIds = array();
$cutOff = 10;
$runningGlobalSymptomId = "";
$symptomResult = mysqli_query($db,"SELECT * FROM comparison_small WHERE (matched_percentage IS NULL OR matched_percentage >= ".$cutOff.")");
if(mysqli_num_rows($symptomResult) > 0){
    while($symRow = mysqli_fetch_array($symptomResult)){
        $dataArray = array();
        $totalSymptoms++;
        if($symRow['current_symptom'] == '1'){
            // Global symptom
            $runningGlobalSymptomId = $symRow['symptom_id'];

            $dataArray['symptom_id'] = $symRow['symptom_id']; 
            $dataArray['row_id'] = "row".$symRow['symptom_id'];  
            $dataArray['symptom'] = $symRow['symptom']; 
            $dataArray['match'] = ""; 
        }else{
            // Local symptom
            array_push($matchedSymptomIds, $symRow['symptom_id']);

            $dataArray['symptom_id'] = $symRow['symptom_id']; 
            $dataArray['row_id'] = "row".$runningGlobalSymptomId."_".$symRow['symptom_id']; 
            $dataArray['symptom'] = $symRow['symptom']; 
            $dataArray['match'] = $symRow['matched_percentage']; 
        }
        $returnArray[] = $dataArray;
    }
}
Jay
  • 744
  • 4
  • 14
  • 30
  • The first thing you need to do is establish *where* the delay is. Is it querying the data from MySQL? (Unlikely, but possible.) Is it creating the JSON in PHP? Is it transferring the data from the server to the browser? Is it rendering the data on the browser? You'll likely find it's a combination of these things, but isolating the primary cause(s) will help you identify where to spend your time. Adding some logging to the PHP and using the Network tab in the browser's devtools will help with that. – T.J. Crowder Aug 16 '21 at 07:39
  • To load the data in a grid, I need to make an Ajax call. The data fetch time is very high (50,000 records). However, for a smaller table with (2000 records) it’s quick. Next I have tried converting the data into JSO format following the instructions here https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql still no improvement. – Jay Aug 16 '21 at 07:52

0 Answers0