PHP Mysql query optimization for report

Question

I have this report which filters data from 5 tables but it is very slow takes around 10 seconds. I tried using index on some columns but it did not help.

Basically the first query is the master and the others are for filtering it if the other queries condition met then it will skip it.

this is the script:

<div class="col-md-12">

    <h3>List of Outstandings</h3>

    <table class="table table-condensed table-bordered table-hover small">
        <thead>
            <tr>
                <th >#</th>
                <th>PR #</th>
                <th>PR Type</th>
                <th>Description</th>
                <th>Dep.</th>
                <th>Date</th>
                <th>Requester</th>
                <th>Assigned to</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $chkk = 0;
            $sql = "SELECT * FROM msr WHERE Status ='Approved'  ";
            $result6 = $connn->query($sql);
            if ($result6->num_rows > 0) {
                $vo = 1;
                while ($row0 = $result6->fetch_assoc()) {
                    $chkk = 0;
                    $MSRID = $row0["MSRID"];
                    $MSRType = $row0["MSRType"];

                    $result4 = "SELECT owner FROM tracking WHERE MSRID='$MSRID' ";
                    $MyRow4 = $connn->query($result4);
                    $row4 = $MyRow4->fetch_assoc();
                    $actionBy = $row4["owner"];


                    $resultusr = "SELECT RFQID FROM rfq WHERE MSRID='$MSRID' AND NOPO='No'  ";
                    $MyRowusr = $connn->query($resultusr);
                    $rowusr = $MyRowusr->fetch_assoc();
                    $rfqcount = mysqli_num_rows($MyRowusr);
                    if ($rfqcount > 0) {
                        $chkk = 1;
                    }

                    $resultusr4 = "SELECT POID FROM po WHERE MSRID='$MSRID'  ";
                    $MyRowusr4 = $connn->query($resultusr4);
                    $rowusr4 = $MyRowusr4->fetch_assoc();
                    $rfqcount4 = mysqli_num_rows($MyRowusr4);
                    if ($rfqcount4 > 0) {
                        $chkk = 1;
                    }

                    $resultusr1 = "SELECT MSRID FROM contract WHERE MSRID='$MSRID'  ";
                    $MyRowusr1 = $connn->query($resultusr1);
                    $rowusr1 = $MyRowusr1->fetch_assoc();
                    $rfqcount1 = mysqli_num_rows($MyRowusr1);
                    if ($rfqcount1 > 0) {
                        $chkk = 1;
                    }
                    if ($chkk == 1) {
                        continue;
                    }
                    ?>
                    <tr>
                        <td>
                            <?php echo $vo; ?>
                        </td>
                        <td>
                            <?php echo $row0["MSRID"]; ?>
                        </td>
                        <td>
                            <?php echo $row0["MSRType"]; ?>
                        </td>
                        <td>
                            <?php echo $row0["purposeofbuying"]; ?>
                        </td>
                        <td>
                            <?php echo depName($row0["DepRequester"]); ?>
                        </td>
                        <td>
                            <?php echo $row0["RequestDate"]; ?>
                        </td>
                        <td>
                            <?php echo reqName($row0["RequestPer"]); ?>
                        </td>
                        <td>
                            <?php echo reqName($actionBy); ?>
                        </td>
                    </tr>
                    <?php
                    $vo++;
                }
            }
            ?>
        </tbody>
    </table>
</div>
</div>

Answer 1

You can use subquery method instead of looping.

Example:

 $sql = "SELECT  *,
        ( SELECT  owner
            FROM  tracking
            WHERE  tracking.MSRID= msr.MSRID
        ) AS _owner, 
        ( SELECT  RFQID
            FROM  rfq
            WHERE  rfq.MSRID= msr.MSRID
              AND  rfq.NOPO='No'
        )  AS _RFQID
    FROM  msr
    WHERE  rfq.Status ='Approved'";