MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Garrick Staples Date:June 28 1999 7:26pm
Subject:Re: Selecting a single record near a given key value
View as plain text  
What about something like:

select max(timestamp),othercolumn,othercolumn from ... where timestamp <
10000;



Michael Widenius wrote:
> 
> >>>>> "Dennis" == Dennis Markwith <dmarkwit@stripped> writes:
> 
> Dennis> I have a table created by the following:
> Dennis> create table Navigation
> Dennis> (
> Dennis>   craftID int not null,
> Dennis>   sensorID int not null,
> Dennis>   timestamp double not null,
> 
> Dennis>   activeData char not null,
> 
> Dennis>   quality float,
> 
> Dennis>   latitude double,
> Dennis>   longitude double,
> Dennis>   speed double,
> Dennis>   heading double,
> Dennis>   depth double,
> Dennis>   dataStatus int,
> 
> Dennis>   primary key(craftID, sensorID, timestamp),
> Dennis>   index altKey(craftID, activeData, timestamp)
> Dennis> );
> 
> Dennis> I want to be able to select a record near or at a particular timestamp
> Dennis> (the closest one) in an effient manner.  When I do a "select" on the
> Dennis> database such as:
> 
> Dennis> select * from Navigation
> Dennis> where
> Dennis> craftID = 1 and
> Dennis> activeData = 'Y' and
> Dennis> timestamp <= 100000
> Dennis> order by timestamp desc
> Dennis> limit 1;
> 
> Dennis> I get the correct record, but I have found that as the database gets
> Dennis> larger it takes longer and longer for the server to execute this select
> Dennis> statement.  What I think the server is doing is getting ALL records <=
> Dennis> 100000 and then applying the order and limit constraints.  If this is
> Dennis> the case, it would take longer and longer to execute as records are
> Dennis> added to the database.
> 
> Dennis> Now, if I limit the select by do the following:
> 
> Dennis> select * from Navigation
> Dennis> where
> Dennis> craftID = 1 and
> Dennis> activeData = 'Y' and
> Dennis> timestamp <= 100000 and
> Dennis> timestamp > 99900
> Dennis> order by timestamp desc
> Dennis> limit 1;
> 
> Dennis> I get much faster results regardless of the size of the database because
> Dennis> the number of records selected to sort and had to the user is much
> Dennis> smaller.  It is possible to use such a statement to get what I want, but
> Dennis> I am not happy at all about arbitrarily coming up with a bottom range
> Dennis> value on the timestamp part of the key.
> 
> Dennis> So, my question is:
> 
> Dennis> Is there a way to ask the database to give me 1 record that is close
> Dennis> (<=) to the timestamp value I ask for in a very efficient way?  Surely
> Dennis> someone has done something like this.
> 
> Dennis> -Dennis Markwith
> Dennis>   Software Engineer
> 
> Hi!
> 
> I have on my personal TODO plans to make your first query VERY fast
> (by reading the index backwards), but it will not have time to add
> this optimization in the real near future (within 1 1/2 month)
> 
> Until this, you have to use the later version...
> 
> Regards,
> Monty
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread6075@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
Thread
Selecting a single record near a given key valueDennis Markwith18 Jun
  • Selecting a single record near a given key valueMichael Widenius28 Jun
  • Re: Selecting a single record near a given key valueGarrick Staples28 Jun