List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 28 1999 6:56pm
Subject:Selecting a single record near a given key value
View as plain text  
>>>>> "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
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