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.
Your specification (presented in a comment on your question) is this:
MDid -- for each doctor -- ...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 */
}
You can reduce the page loading time by using LIMIT in query and fetch result with pagination. So the all records will not come in a single result set but that can be possible via paging
SELECT DateLastSaved,MDid,FileName FROM InitialLog WHERE FileName NOT LIKE '%Patient Names%' LIMIT $offset $perpage
Here $offset is the index number of your results (page_number * $perpage) and $perpage is the how much rows you want to fetch in a single query.
Try to use HashMap.
In php terms it is simple associative array.
$key=$row3['MDid']."||".$row3['FileName'];
$date=$row3['DateLastSaved'];
if(!isset($datearray[$key]))
{
$datearray[$key]=$date;
}
else
{
...
}
It has constant key search time and should be excellent for task you tried to solve.
On first thought i would say you are trying to get the most recent 'DateLastSaved' for each 'MDid' and 'FileName' combination.
If so, i would simply alter the SQL to
SELECT concat(MDid, FileName) tobestored,
max(DateLastSaved) maxDateLastSaved
FROM InitialLog
GROUP BY 1
This query would return only one row per 'MDid' and 'FileName' combination containing the combined value and the most recent date.
No more need to loop through all fetched records to search arraykeys or compare dates.
The only thing that i am not sure about is this part
if($aryear==$pryear && $armonth==$prmonth) {
if($prday>$arday) {
$datearray[$key]=$date;
}
}
Looks like you want to keep the older 'DateLastSaved' if it is from a previous month or year. If this applies, my change to your query does not give your desired data.