Here is an alternative programming solution that may work for others who use MyISAM IF (important) you don't care if an update has happened during the middle of the queries. As we know MyISAM can cause table level locks, especially if you have an update pending which will get locked, and then other select queries behind this update get locked too.
So this method won't prevent a lock, but it will make a lot of tiny locks, so as not to hang a website for example which needs a response within a very short frame of time.
The idea here is we grab a range based on an index which is quick, then we do our match from that query only, so it's in smaller batches. Then we move down the list onto the next range and check them for our match.
Example is in Perl with a bit of pseudo code, and traverses high to low.
# object_id must be an index so it's fast
# First get the range of object_id, as it may not start from 0 to reduce empty queries later on.
my ( $first_id, $last_id ) = $db->db_query_array(
sql => q{ SELECT MIN(object_id), MAX(object_id) FROM mytable }
);
my $keep_running = 1;
my $step_size = 1000;
my $next_id = $last_id;
while( $keep_running ) {
my $sql = q{
SELECT object_id, created, status FROM
( SELECT object_id, created, status FROM mytable AS is1 WHERE is1.object_id <= ? ORDER BY is1.object_id DESC LIMIT ? ) AS is2
WHERE status='live' ORDER BY object_id DESC
};
my $sth = $db->db_query( sql => $sql, args => [ $step_size, $next_id ] );
while( my ($object_id, $created, $status ) = $sth->fetchrow_array() ) {
$last_id = $object_id;
## do your stuff
}
if( !$last_id ) {
$next_id -= $step_size; # There weren't any matched in the range we grabbed
} else {
$next_id = $last_id - 1; # There were some, so we'll start from that.
}
$keep_running = 0 if $next_id < 1 || $next_id < $first_id;
}
