SQL_CALC_FOUND_ROWS / found_rows() vs loop counter

[7 reeds]

Hi,

<edit>I am using perl 5.8.8, MySQL 5.1.51 and am connecting to the db via DBI->connect_cached() <edit>

I am experimenting with using SQL_CALC_FOUND_ROWS and FOUND_ROWS() to tell me the number of items returned from a query with a LIMIT clause. I have an ever growing table with (currently) 348735 records -- based on a SELECT count(*) FROM table. As i type this that number may grow by a few or a dozen.

Last evening I was running a perl script to test the numbers and issued the query:

$tmp = <<EOF;
SELECT SQL_CALC_FOUND_ROWS
    *
FROM
    my_table
ORDER BY
    date ASC
LIMIT 0, 999999999
EOF

$sth = &returnQuery($DBH, $tmp);
for ($i = 0; $row = $sth->fetchrow_hashref; $i++) {
    if ($i && ($i % 2000) == 0) {
        print STDOUT "\tSleeping after $i\n";
        sleep 2;
    }
}
$sth->finish;
print STDOUT "Number of rows operated on = $i\n";

$sth = &returnQuery($DBH, "SELECT FOUND_ROWS()");
@cnt = $sth->fetchrow_array;
$sth->finish;
print STDOUT "returned rows = $cnt[0]\n";

The last time i ran this yesterday it returned:

# ./copyDB
        Sleeping after 2000
        Sleeping after 4000
        Sleeping after 6000
        Sleeping after 8000
        Sleeping after 10000
[...]
        Sleeping after 344000
        Sleeping after 346000
        Sleeping after 348000
Number of rows operated on = 348690
returned rows = 279260

So, last night there were a few less records in the table than there are now. That is to be expected. The question is why is the number returned by FOUND_ROWS() so much lower than the loop counter?

I figure that I did something wrong but I can't see it yet.

ideas?

Seven



Edited 1 time(s). Last edit at 07/18/2012 01:02PM by 7 reeds.