Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re: multi-dimensional range lookup

by roboticus (Chancellor)
on May 09, 2007 at 12:38 UTC ( #614373=note: print w/replies, xml ) Need Help??

in reply to multi-dimensional range lookup


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)
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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://614373]
and the universe expands...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2018-06-25 01:00 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.