The short version:
Don't worry about memory consumption. Just don't try to fetch the entire result-set at once.
Your idea of using a separate table to list every text message and then updating the row when you know if it succeeded or not is generally the correct approach (whether you do it in cron or not doesn't really matter).
If you are concerned that your SMS provider might drop some of your requests, then you can implement your own queuing mechanism using a ActiveMQ or something similar. However, that kind of defeats a large part of the purpose of using a provider. They should be using their own queue so that you do not need to worry about it.
Details:
The SMS service should be notifying you of success or failure.
Most high-volume SMS services queue your messages, and send them out in chunks of n messages at a time. Then, they will notify you via some sort of callback or web hook, which messages have succeeded and which have failed.
Most of them also provide API's that let you check if a certain message(s) has been sent or not. You need to leverage those features.
I think you're on the right track with your cron approach.
One option is to never "pull" the records. Instead, have a column on the existing table that specifies if it is waiting for a message to send or not. This way, instead of doing a SELECT and dealing with hundreds of thousands of rows, you do a simple UPDATE and then when each callback comes from the API, you can re-update the rows with success/failure.
If you are in a situation where you might be sending multiple messages at a time for each row of data, then obviously this won't work. You will have to have a separate table with a row for every message that you want to track.
As far as your memory concerns, I don't think it is an issue. Just don't fetch the entire result set. Instead, fetch each row individually. This will prevent mysql from returning the entire data set and thus you wont need to keep it in memory.
From php.net
As mysqli_fetch_all() returns all the rows as an array in a single step, it may consume more memory than some similar functions such as mysqli_fetch_array(), which only returns one row at a time from the result set. Further, if you need to iterate over the result set, you will need a looping construct that will further impact performance. For these reasons mysqli_fetch_all() should only be used in those situations where the fetched result set will be sent to another layer for processing.
Edit / amendment
to address the comment/question:
I can't pull just one entry per chron - that would take forever... I
understand I shouldn't fetch the entire result set at once too, that's
what led me to ask "then how else can I do it?
In PHP (using mysqli with mysqlnd), when you do a query, it doesn't actually return the data. It prepares the data to be returned based on your query, but it doesn't return it.
When you use fetch_all you are asking for the entire result.
When you use fetch_array, you are asking for the next result, and you are telling mysql to move the result cursor, so that you can get the next result after that. As long as you don't store every single result in memory (in separate variables), then there is no memory issue. Just use the row as you need to use it, and then get the next one.
Whether it's a cron job or not doesn't matter.
You do not need to call the script over-and-over, once for each row. The script handles every row in a single invocation. It just does it reading one-row-at-a-time, as to save memory.
Here is a script example:
$mysqli = new mysqli("host", "user", "pass", "db");
$query = "SELECT * from TextMessages";
$result = $mysqli->query($query);
while ($row = $mysqli->fetch_array($result))
{
//this is the only thing you store in memory, one single row at a time
$row = $result->fetch_array(MYSQLI_ASSOC);
//go send the text message and do whatever else you need to do
if ($row["SomeSmsToken"] == null && $row["TextHasAlreadyBeenSentOrDateSentOrWhatever"] == false)
{
//$someSmsToken = $myTwilioObject->SendByRow($row);
//$this->UpdateRowToTellItThatItHasBeenSentToProviderAndIsWaitingForResponse($row,$someSmsToken);
//..etc...
//then go to the next row.
}
}
$result->free();
And then in some callback script you would do something like this.
$mysqli = new mysqli("host", "user", "pass", "db");
$query = "SELECT * from TextMessages where SomeSmsToken = '".$_POST["SomeTokenSentFromProviderInCallback"]."'";
$result = $mysqli->query($query);
while ($row = $mysqli->fetch_array($result))
{
$someObject->UpdateRowToSayThatTheTextWasSentOrItFailed($row,$_POST["SomeStatusSentFromProviderInCallback"]);
}
$result->free();
You can also use mysqli_free_result when you're done, to free up any consumed memory by php's mysql driver.
From php.net:
You should always free your result with mysqli_free_result(), when your result object is not needed anymore.
EDIT:
If you want some sort of smart way to deal with "what if the script times out", I would suggest having a cron run every minute.
When it runs it should check if it is already running, and, if it isn't already running, then you run it.
The script will do it's work until it times out.
Then within a minute, cron will start it again and, since it isn't running, it will run again and pick up wherever it left off.
