Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

If you just want to see whether a value appears in a column of a table, performing a count(*) is a fearfully expensive way of going about that. At the worst, you will perform a full table scan.

The query would be better formulated as select 1 from members where nickname='...'

If this is just a once-off, the simplest approach would be to use the do method, with something (error-checking notwithstanding) like:

sub nick_exists { return $db->do( "select 1 from members where nickname=" . $db->quote($_[0]) ); }

If, on the other hand, you know you'll be pounding on this routine in an inner loop, it might then be worthwhile recasting it as a prepared statement:

BEGIN { my $ss = $db->prepare( 'select 1 from members where nickname=?' ) or d +ie; sub nick_exists { $ss->execute($_[0]); my $res = $ss->fetchrow_arrayref; $ss->finish; $res and defined $res->[0] ? 1 : 0; } }

The beauty of this approach is that you don't have to change your code. The access to the database is abstracted away in the nick_exists routine.

And as if that wasn't enough, you could further add

use Memoize; memoize('nick_exists');

... although you want to be careful with that. The program will continue to assume that a nick that has just been taken is still available. That would be a bug. But, used with care, this can provide a healthy speed boost.


In reply to Re: Odd Database Behavior by grinder
in thread Odd Database Behavior by SpritusMaximus

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others wandering the Monastery: (5)
    As of 2021-04-16 20:55 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?