MySQL Forums
Forum List  »  Perl

SQL_CALC_FOUND_ROWS / found_rows() vs loop counter
Posted by: 7 reeds
Date: July 18, 2012 10:46AM

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.

Options: ReplyQuote


Subject
Written By
Posted
SQL_CALC_FOUND_ROWS / found_rows() vs loop counter
July 18, 2012 10:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.