find next lowest value in lookup table?
I have a table of IDs and data values, and I want to find the next lowest value in a lookup table for each of these records.
The query looks sort of like this:
create table data (id int, val double);
create table lookup (v_low double);
...
# note: table "lookup" has fewer records than table "data". The v_low values correspond to the lower bound of several interesting ranges. E.g., data.value might have 14, 17, 29, 71 and lookup.v_low could have 0, 20, 40, 60.
# this is the tricky query:
select d.id, d.val max(l.v_low) as v_low from data d, lookup l where l.v_low <= d.val group by 1, 2;
In theory, mysql could sort table "data" into groups (maybe using an existing index). Then it could use a range search in "lookup", to find the greatest v_low value that is less than each val value. But I'm not sure whether it's doing this, or doing a full table scan in lookup.
Does anyone know how well MySQL optimizes this type of query, and can you suggest a way to rewrite it to work quickly?
Thanks!
Subject
Views
Written By
Posted
find next lowest value in lookup table?
6141
March 21, 2008 06:03PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.