While working on a project, I came across the need for a script to run a loop through a table and process some commands. However, due to the size of the table, this surely would take longer than the default 30 seconds that is setup in most configurations. However, I didn’t want to do any limits. I wanted my script to determine when it was nearing the timeout and then stop, otherwise try to process more. So a standard LIMIT in mysql wouldn’t do it.
Much to my surprise, MySQL doesn’t offer a way to just do a OFFSET. You have to use the LIMIT with OFFSET or none at all. This was really annoying as I thought I would have to go back to limiting the query size.
Well, then I realized, that this could be solved another way. I just added a column and populated it with an incremental numbers. Then I told my script to ORDER BY that column id using ascending. Now with that in play, I simply just added a WHERE to my query and told it not to do anything below a certain id. The certain id comes from a variable that is passed from the user and cleaned up (safety first!). The script after processing the needed commands, updates this variable. Finally when it is time to pause the script so it doesn’t time out, that variable is sent with the forwarding url. This method allows the script to pick up where it left off again when it starts up.
Seems like a very simple work around, although if I didn’t have the id column, it wouldn’t of worked.