Yes, the database will only ever be used for selects and to find intersections. There will never be any insertions, deletions, or anything else, except by administrators on a very exceptional basis should an error be found in the data.

As for pre-calculation, yes, I plan to do what can be done. There are a couple hundred queries that will be repeated often, as their results form the base line for statistical comparison of other queries. But there are 9-million possible 'other queries', each with equal probability of being asked. It's these I think maybe can be reduced to simple selects.

Perrin does, however, give me reason to pause. My fields are smallint, and no retrieval will be larger than 10,000 single-value records. Normally I'd be doing the intersection of a 10,000 element array with a ~4,000 element array of smallints. I have compound primary key as the sole index. So before I commit to heavily to the Perl solution, I'll try a few different database schemas and indices. Nice to nkow I have the Perl solution in my pocket, though.

Off I go ...

In reply to Re: Basic Perl array intersection faster than mysql query join. by punch_card_don
in thread Basic Perl array intersection faster than mysql query join. by punch_card_don

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, details, 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, summary, 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.