List:General Discussion« Previous MessageNext Message »
From:Sebastiaan van Erk Date:November 13 2006 1:44pm
Subject:How do I do this query efficiently?
View as plain text  
Hi all,

I have the following simple table:

CREATE TABLE data (
   id int NOT NULL,
   version int NOT NULL,
   value int NOT NULL,

  PRIMARY KEY (id, version)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

What I would like to do is to find all the values for the latest 
versions, that is, for every "id" I want exactly one row, namely the row 
with the maximum value of "version". Without using SQL I can think of a 
very efficient way for the query optimizer to accomplish this for me, 
namely by using the primary key to iterate through each value for "id" 
and then looking for the maximum "version", which is essentialy O(1) 
because it's the last entry in the index for that specific id. However, 
I don't know how to do this query in SQL, the closest I got was:

select id, version, value from data d1 where version = (select 
max(d2.version) from data d2 where d2.id = d1.id)

However this is extremely inefficient and takes much to long (in the 
order of 5 seconds on the data I have).

Does anybody know an efficient way to do this?

Thanks in advance,
Sebastiaan



Thread
How do I do this query efficiently?Sebastiaan van Erk13 Nov
  • Re: How do I do this query efficiently?Peter Brawley13 Nov
    • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
    • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
      • Re: How do I do this query efficiently?Peter Brawley13 Nov
        • Re: How do I do this query efficiently?Rolando Edwards13 Nov
        • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
Re: How do I do this query efficiently?Peter Brawley16 Nov
  • Re: How do I do this query efficiently?Manuel Vacelet17 Nov