Re: SELECT COUNT and DBI: rows

OK I'm really confused indeed right now.

I've got working code which goes like this:

 $sth = $dbh->prepare( "
 SELECT  
   ## long complicated WHERE stuff
  " ) || die "Error: " . $dbh->errstr;

  $sth->execute() || die "Error: " . $dbh->errstr;    
  if( $sth->rows > 0 ){
     ## print out some results
  }
[download]

And according to this thread, I shouldn't do that, and I shouldn't rely on the results?

What should I do instead? Should I do the whole thing twice, like this?

 $sth = $dbh->prepare( "
 SELECT COUNT(*) As Total
   ## long complicated WHERE stuff
  " ) || die "Error: " . $dbh->errstr;

  $sth->execute() || die "Error: " . $dbh->errstr;    
  if( $Total > 0 ){
     ## only if it passes this condition, do the REAL
     ## select and print out the results
  }
[download]

That seems bizarrely innefficient.

($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

Comment on Re: SELECT COUNT and DBI: rows Select or Download Code

Replies are listed 'Best First'.
Re: Re: SELECT COUNT and DBI: rows by dws (Chancellor) on Sep 29, 2003 at 02:28 UTC
What should I do instead? Should I do the whole thing twice, like this? ... That seems bizarrely innefficient. I was deep into this issue several years back, and I wish I still had my old notes so that I tell you which of the then-major RDMBSs didn't provide a count at `SELECT` time. It had to do with how that particular RDMBS used lazy evaluation to produce its result set. (Lazy eval meant that no count was avaible until the result set was exhausted. I doubt that's what you want.) This might not be an issue with the current generation of significant databases. Perhaps others who work with the major vendors can chime in and let us know. If you've got something working with MySQL, a strategy of sticking with what you've got, but noting that there might be an issue should you port, may be good enough.	[reply] [d/l]
Re: Re: Re: SELECT COUNT and DBI: rows by Cody Pendant (Prior) on Sep 29, 2003 at 04:07 UTC
Thanks for that. So DBI tells you not to rely on a rows count in general terms, but MySQL is a specific instance when you can? I'm happy with that. Mine's not the kind of code you port. `($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss') =~y~b-v~a-z~s; print` [download]	[reply] [d/l]
Re: Re: SELECT COUNT and DBI: rows by CountZero (Bishop) on Oct 06, 2003 at 20:32 UTC
If you want to do some function on your database, but only if there is something there to do it on, you could do the "two step" you suggested (COUNT () is very fast and fuly optimized in mySQL, so it does not "hurt" your database much), BUT* your queries are not atomic and it could be that between your first step and the second step someone changed (deleted or added) the number of records so the result of your first test becomes invalid. Therefore, only do the second step and wrap the fetch-function in a `while`-loop. If you get no results out of your SELECT, the code inside the `while`-loop will not get executed and no harm is done. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]