danmcc:

You've gotten plenty of interesting responses for doing the in-memory data structure. However, RL alludes to database indexing, and I thought the point was worth expanding on. If you have indexes on the color columns, the database access ought to be pretty zippy.

If you're missing those indices, then the database will have to do a table scan (i.e., read every record to see if it matches the criteria). With those indices, the database software can just reach right in and grab the rows of interest.

Making a single color lookup index on the three columns r, g, b would be a good idea if you're always going to supply all three components. However, if you want to just query for individual components, you may want to have a different index on each color. This way, the database server won't have to drop down to a table scan if you choose the last color in your index. So if you have a query like:

select photo_id from colors where g in (78,120,180) and blue in (23,25
+,27)

[download]
then the database could just read the list of photos for the acceptable green components from the index, and the same for blue, and then return the photos in both lists. If you have enough photos in your database, it will be *much* faster than a table scan.

...roboticus

In reply to Re: multi-dimensional range lookup by roboticus
in thread multi-dimensional range lookup by danmcc

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.