1

I have a table like so:

id    min    max    version    data
 1     1      10       1         a
 2    11      20       1         b
 3    21      30       1         c

 4     1      10       2         a
 5    11      20       2         b
 6    21      30       2         c

min, max represent values of key. Each (min, max) row within the given version is guaranteed to have mutually exclusive key intervals.

Suppose I have a key value of 5 which and I want the latest version of data for that key. This means, I want to select row with id = 4.

Normally I want to select the set with the latest version, but sometimes I may specify the version number explicitly.

What I have now is this:

select * from range_table where 5 between `min` and `max` and ver = 2;

Question: is there a way to select max version automatically (max ver), without specifying it explicitly? (By "efficiently" I mean without examining all tables rows.)

To Recreate Table

drop table range_table;
CREATE TABLE `range_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `min` int(11) NOT NULL,
  `max` int(11) NOT NULL,
  `ver` int(11) NOT NULL default 1, 
  `data` CHAR NOT NULL,

  PRIMARY KEY (`id`),
  unique key ver_min_max(ver, `min`, `max`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into range_table (`min`,`max`, ver, data) values
(1, 10, 1, 'a'),
(11, 20, 1, 'b'),
(21, 30, 1, 'c'),
(1, 10, 2, 'a'),
(11, 20, 2, 'b'),
(21, 30, 2, 'd');
Dennis
  • 7,907
  • 11
  • 65
  • 115
  • What is inefficient about the one you have? – AdamMc331 Dec 29 '14 at 17:17
  • 1
    I'm not sure if I understood well but having a key value of 5 according to the query means that there will be row with id=4 selected , not id=6 ? –  Dec 29 '14 at 17:17
  • I don't know if it's more efficient, but one way to rewrite it could be `WHERE 5 BETWEEN min AND max`. – AdamMc331 Dec 29 '14 at 17:19
  • @Mc: query execution plan examines all rows. I think there is a way to zero-in on the one row using the index. – Dennis Dec 29 '14 at 17:19
  • update: not anymore,... not sure why it was examining all rows before, now it says 1 row is examined using my `ver_min_max index` Just one question remains, see updated question section in my question.. – Dennis Dec 29 '14 at 17:25

3 Answers3

1

You could take the first row ordered by ver desc...

select * from range_table where 5 between `min` and `max` order by ver desc limit 1;
dotjoe
  • 26,242
  • 5
  • 63
  • 77
0

If you care about performance, then, depending on the size and/or selectivity of the columns, you can add an index to the min or max column. If the number of versions remain low for each min-max, then your query will be optimized.

Nicola Ferraro
  • 4,051
  • 5
  • 28
  • 60
0

Please try the following to select always the latest version

select * from range_table where @key between `min` and `max` and ver = (select max (a.ver) as max_ver from range_table as a where @key between a.`min` and a.`max`)

where @key would be a given key value.