SQL Help

[Crusty]

I have a table named prices with 2 columns, date and price;

Sample data
2006-10-31,5.00
2006-10-30,4.75
2006-10-29,5.50
2006-10-28,4.25

I need to get a list of price differences ie.

5.00 - 4.75
4.75 - 5.50
5.50 - 4.25

I can't for the life of me figure out how to offset the join by 1 day so that I can subtract the 2 fields.

The only way I have figured out how to do it is really slow... which can not be.

SELECT p1 - p2 FROM (SELECT prices1.date AS p1d, prices2.date AS p2d,prices1.price AS p1, prices2.price AS p2 FROM prices prices1, prices prices2) temp WHERE DATE_SUB(temp.p1d, INTERVAL 1 DAY) = temp.p2d;

That's slow and ugly 🙁

Any ideas?

this is on a mySQL 5.0.22 server

 

[kpilkington]

Don't think you need to use a nested table. See if this is any quicker:

 

[Crusty]

That's horribly slow. The table in question is rather large, almost 18 million rows.

This query needs to be able to generatethe changes on anywhere from 90-260 days as fast as possible. Using date_sub it runs in around 15s. I'm putting a new index on the table as we speak that is date - 1 day. I think that is the only way I will be able to get this done.

 

[kpilkington]

OK.

What did your WHERE clause look like when you tried my query? If you've got 18million rows I'm guessing you've got additional columns in the primary key besides date, then? Did you account for those columns in your where clause and inner join clause?

I'm assuming the 90-260 days you mention is an inclusive range (i.e., it's asking for the price difference between each set of consecutive days within a variable 90 to 260 day period).

Is your WHERE clause written so that it reduces the price table to a set of 91 to 261 rows before it tries to join p1 to p2?

ex.

 

[Crusty]

That's precisely what I am doing. There's also an ID field in the index. There are about 5100 unique ID's and dates taht range from the current date back into 1986.

But I figured out the problem, I have a custom function I use for my date addition subtraction since these dates are based on an odd calendar and it was returning a datetime type, which was getting compared to a date column in the tables.... and apparently because of that mysql was skipping the index.

Fixed the function, and it now runs in well under a second even for returning 1000 dates.