当数据库有大量数据时，如何避免在php中执行时间慢

Question

My code is taking a lot of time to execute as my database has lots of data. Initially when there were 5000 rows it worked fine, but now my data has increased and it takes approximately 2 minutes to load. My PHP code is:

$result=$db->query("SELECT DateLastSaved,MDid,FileName FROM InitialLog");

$filesarray=array();
$datearray=array();

while($row3=mysqli_fetch_array($result))
{
    $tobestored=$row3['MDid']."||".$row3['FileName'];

    $key=array_search($tobestored,$filesarray);
    $date=$row3['DateLastSaved'];                


    if(!is_numeric($key))
    {
        $filesarray[]=$tobestored;
       $datearray[]=$date;
    }
    else
    {
        $aryear=date("Y",strtotime($datearray[$key]));
        $armonth=date("m",strtotime($datearray[$key]));
        $arday=date("d",strtotime($datearray[$key]));

        $pryear=date("Y",strtotime($date));
        $prmonth=date("m",strtotime($date));
        $prday=date("d",strtotime($date));

       if($aryear==$pryear && $armonth==$prmonth)
       {
           if($prday>$arday)
           {
               $datearray[$key]=$date;
           }
       }
    }
}

Array is taking a lot of time in this code.

(From a comment). My purpose is to get all the file names of each MD as well as Date Last saved. If from the list, a specific file is repeated and is present in January, February and so on, I want the file from January only, that is the oldest month and also from that month I want the last in that month, that is if it exists on 1st Jan, 2nd jan, 30th jan, I would like to get the 30th Jan one.

Answer 1

Your specification (presented in a comment on your question) is this:

• For each distinct value of MDid -- for each doctor -- ...
• Find the first month in which your table contains any row ...
• Then find the latest row in that first month, and ...
• Present that row in the result set.

Pro tip: try to formulate this kind of specification as clearly as possible before starting to write code. The hairier the specification, the more important it is to have clarity. This specification has hair.

Let's build this up as a MySQL query. First, you need to find the first month each MDid appears. This subquery does that using LAST_DAY(). In this application, think of LAST_DAY(date) as meaning MONTH_AND_YEAR_OF(date).

                      SELECT MDid, MIN(LAST_DAY(DateLastSaved)) FirstMonth
                        FROM InitialLog
                       GROUP BY MDid

This aggregating subquery yields one row per doctor, with the very last day of the first month in it. (That's what MIN(LAST_DAY(DateLastSaved)) does.)

Pro tip: many people find it helpful to test their subqueries in phpMyAdmin or some other command line SQL program.

Let us now use it in another subquery to find the latest date that occurs in that first month in the table.

           SELECT MAX(DateLastSaved) LastInMonth,
                  a.MDid
             FROM InitialLog a
             JOIN (
                           SELECT MDid, MIN(LAST_DAY(DateLastSaved)) FirstMonth
                             FROM InitialLog
                            GROUP BY MDid
                  ) b  ON a.MDid = b.MDid 
                      AND LAST_DAY(a.DateLastSaved) = b.FirstMonth
            GROUP BY a.MDid

Here we join the InitialLog table to the first subquery, using the ON clause to eliminate all the rows of InitialLog that aren't in the first month (that's what LAST_DAY(a.DateLastSaved) = b.FirstMonth does).

Cool. this subquery gives us the date in your specification for each doctor. Finally, we have to go get the original row, containing the FileName as well as the other columns. We already know the MDid and the DateLastSaved.

This is the final query.

SELECT orig.DateLastSaved, orig.MDid, orig.FileName
  FROM InitialLog orig
  JOIN (  /* that subquery */
       ) datechoice ON orig.MDid = datechoice.MDid
                   AND orig.DateLastSaved = datechoice.LastInMonth
 ORDER BY orig.MDid /* or whatever order you want */

This gives one row per MDid. It uses the DBMS to implement your specification, rather than looking at all the rows of your table. If your table has an index on (MDid, DateLastSaved) this query will probably scale up very well when you have tens of thousands of doctors and decades of data in your table.

Putting it all together, your php program is this. Now you may be able to guess why it's called Structured Query language.

$docs_first_monthend_bill_query = <<<ENDQUERY
SELECT orig.DateLastSaved, orig.MDid, orig.FileName
  FROM InitialLog orig
  JOIN (  
           SELECT MAX(DateLastSaved) LastInMonth,
                  a.MDid
             FROM InitialLog a
             JOIN (
                           SELECT MDid, MIN(LAST_DAY(DateLastSaved)) FirstMonth
                             FROM InitialLog
                            GROUP BY MDid
                  ) b  ON a.MDid = b.MDid 
                      AND LAST_DAY(a.DateLastSaved) = b.FirstMonth
            GROUP BY a.MDid
       ) datechoice ON orig.MDid = datechoice.MDid
                   AND orig.DateLastSaved = datechoice.LastInMonth
 ORDER BY orig.MDid /* or whatever order you want */
ENDQUERY;

$result=$db->query($docs_first_monthend_bill_query);
while($row3=mysqli_fetch_array($result)) {
    /* process the interesting rows */
}