Converting php loop to simple MySQL query
Sorry if this is a stupid question. And thanks for your time. I searched around and found similar problems / answers but didn't find what I am looking for.
I'm used to making all my MySQL calls through PHP, but recently I was tasked with a very large database table with 5,000,000 stock records. PHP works well for me, but it takes HOURS to complete. I think it will be much faster with simple MySQL calls ... "Simple" doesn't get lost on me.
My_Table:
| ID | My_Stockbroker | My_Start_Date | My_End_Date | POST_SIZE |
| 1 | DAVE | 2000-06-19 | 2000-06-19 | 2 |
| 2 | DAVE | 1999-03-02 | 2002-09-06 | 0 |
| 3 | DAVE | 1999-03-14 | 2002-12-10 | 0 |
| 4 | SAM | 2000-06-19 | 2000-06-19 | |
Target table to update - My_Table
- I want to update all POST_SIZE' values on
My_Table`
My PHP is very simple:
- Originally POST_SIZE
- Get one single
My_Stockbroker
name from My_Table = "DAVE" - count any other records (not counting the processed record) from My_Table that = "DAVE" AND falls into a specific date range that is
My_Table
.My_Start_Date
<"ID1".My_Table
...My_Start_Date
&& &My_Table
.My_End_Date
> "ID1".My_Table
...My_Start_Date
POST_SIZE ++;
My PHP code looks like this:
// Get 1 record
$sql = "SELECT `ID`, ` My_Stockbroker`, `My_Start_Date`, from `".$Table."` WHERE ` My_Stockbroker` != '' AND `POST_SIZE` = '' LIMIT 1"; //
while($row = $result->fetch_assoc()){// while 1 record result
$ My_Stockbroker_To_Examin_ID = $row["ID"];
$ My_Stockbroker_To_Examin = $row[" My_Stockbroker"];
$ My_Stockbroker_To_Examin_My_Start_Date = $row["My_Start_Date"];
}
/////// GET ALL RECORDS FOR SPECIFIC My_Stockbroker that fall within specific date ///////
$sql = "SELECT `ID` FROM `".$Table."` WHERE `My_Start_Date` <
'".$My_Stockbroker_To_Examin_My_Start_Date."' AND `My_End_Date` >
'".$My_Stockbroker_To_Examin_My_Start_Date."' AND `My_Stockbroker` =
'".$My_Stockbroker_To_Examin."' AND `ID` != '".$ My_Stockbroker_To_Examin_ID ."'"; //
if($result = $conn->query($sql)){//
$POST_SIZE = $result->num_rows;
}
// now write back the number of rows found
$sql = "UPDATE `".$Table."` SET `POST_SIZE` = '".$POST_SIZE."', WHERE `ID` = '".$My_Stockbroker_To_Examin_ID."'; ";
As I said, the PHP code works flawlessly for me. I just don't have time to wait for it to end.
How can this be achieved - on one table - using only MySQL?
Read single record / count All records in the date / record range up to POST_SIZE
Do I need to create a bunch of temporary tables on the fly and join, etc.? Or can this be achieved in a MySQL loop? Is MySQL Faster? - Or maybe I'm wrong with my PHP?
source to share
I haven't tried this sql, but it should be something like
UPDATE My_Table set Post_size = (SELECT COUNT(ID) FROM My_Table
WHERE ID <> My_Stockbroker_To_Examin_ID
AND My_Start_Date < My_Stockbroker_To_Examin_My_Start_Date
AND My_End_Date > My_Stockbroker_To_Examin_My_Start_Date
AND My_Stockbroker = My_Stockbroker_To_Examin)
You can replace these variables with the values you want.
source to share
You can do this whole process in a single query request like
SELECT `ID`
FROM
(
SELECT `ID` as MSID,
`My_Stockbroker` as MSB,
`My_Start_Date` as MSD
from My_Table
WHERE `My_Stockbroker` IS NOT NULL AND `POST_SIZE` IS NULL
ORDER BY `ID` LIMIT 1
) tab
WHERE `My_Start_Date` < MSD
AND `My_End_Date` > MSD
AND `My_Stockbroker` = MSB
AND `ID` != MSID;
source to share