Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Comment on

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

Here’s an even more clever solution, I think:
SELECT DISTINCT ID FROM table ORDER BY ID;

Now, just read the rows.   If the first ID that comes back is, say, 50, then you know that IDs 1-49 are not in the table.   Read some more, and say you get 51, 52, 53, 60.   Okay, the next range of missing IDs is therefore 54-69.   Any time there is a gap in the returned sequence (when using ORDER BY), the keys in that space do not exist.

Furthermore, if the rows are indexed by this field, using the customary B-tree style index, then SQL does not have to physically sort the rows; it simply reads them in index order.   (Use the EXPLAIN verb, separately (not in your program), to confirm that this is what it will do.)

It’s an old (punched...) card trick.

If you find that the table is not so conveniently indexed and you are willing to scan it multiple times, you could read ranges of IDs, note them in a hash, and then loop through that range, one value at a time, finding the keys that are not in your hash.   Then empty the hash and repeat.   Grabbing 10,000 records or more in each chunk should not be a concern.


In reply to Re: Help with database query by sundialsvc4
in thread Help with database query by htmanning

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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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 taking refuge in the Monastery: (6)
    As of 2014-12-23 02:00 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (133 votes), past polls