Thanks Shane,
It works reasonably well. It gets the right answer,
but I guess my data set is much larger than your test.
Please consider the appended data.
The first two SQL statements are directly comparable.
My Left join is marginally simpler, as shown by
EXPLAIN, and runs to completion in about 3 seconds
(elapsed real time), as opposed to about 30 seconds
for the two views. It makes a little sense, though,
in that according to EXPLAIN, the LEFT JOIN needs to
consider a dramatically smaller number of rows. What
I find puzzling, though, is that it takes less time to
get the report for 28 stocks at a given time than it
does to get the report for 1. (Both take about 30
seconds, but for 28 stocks, it takes about 0.005
seconds less time ;-)
This is a case where LEFT JOINS appear to be much
faster than subqueries.
I appreciate all your help, but I am struggling to
figure out how best to adapt my LEFT JOINs in your
VIEWs, so that the latter benefit from the speed of
the JOINs. The heart of my problem is to figure out
how to use a stock_id in the WHERE clause.
One thing I am not certain of is, "Is there a way to
preserve the logic of the WHERE clauses by replacing
the WHERE clause, which I use to sample the time
series at 22 days ago, 66 days ago, 132 days ago &c.,
by a "GROUP BY" clause, grouping by stock_id? If so,
might that, along with an additional LEFT JOIN, get me
the result I am after?
I created a stored procedure that takes an id argument
(and can usefully invoke it on any stock_id in the
database), but the problem remains as to how to
construct a record set by applying the procedure to
each id in a set of ids returned, e.g., by SELECT
stock_id FROM stocks;
Ted
=========== test data =============
EXPLAIN SELECT A1.stock_id,
A1.price_date,
A1.adjusted,
A2.price_date AS pd22,
100.0 * (A1.adjusted -
A2.adjusted)/A2.adjusted AS gl22pc,
A3.price_date AS pd66,
100.0 * (A1.adjusted -
A3.adjusted)/A3.adjusted AS gl66pc,
A4.price_date AS pd132,
100.0 * (A1.adjusted -
A4.adjusted)/A4.adjusted AS gl132pc,
A5.price_date AS pd264,
100.0 * (A1.adjusted -
A5.adjusted)/A5.adjusted AS gl264pc
FROM
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1) AS A1
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 22) AS A2
ON A1.stock_id = A2.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 66) AS A3
ON A1.stock_id = A3.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 132) AS A4
ON A1.stock_id = A4.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 264) AS A5
ON A1.stock_id = A5.stock_id;
+----+-------------+-------------+--------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref | rows |
Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+-------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL
| NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived3> | system | NULL
| NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived4> | system | NULL
| NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived5> | system | NULL
| NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived6> | system | NULL
| NULL | NULL | NULL | 1 | |
| 6 | DERIVED | stockprices | ref | PRIMARY
| PRIMARY | 4 | | 17442 | Using where |
| 5 | DERIVED | stockprices | ref | PRIMARY
| PRIMARY | 4 | | 17442 | Using where |
| 4 | DERIVED | stockprices | ref | PRIMARY
| PRIMARY | 4 | | 17442 | Using where |
| 3 | DERIVED | stockprices | ref | PRIMARY
| PRIMARY | 4 | | 17442 | Using where |
| 2 | DERIVED | stockprices | ref | PRIMARY
| PRIMARY | 4 | | 17442 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+------+-------+-------------+
10 rows in set (0.08 sec)
EXPLAIN SELECT * FROM stock_price_history WHERE
stock_id = 1;
+----+--------------------+-------------+-------+---------------+---------+---------+-------------------+--------+----------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
+----+--------------------+-------------+-------+---------------+---------+---------+-------------------+--------+----------------+
| 1 | PRIMARY | <derived3> | ALL | NULL
| NULL | NULL | NULL |
494 | Using where |
| 3 | DERIVED | ST | index | NULL
| PRIMARY | 4 | NULL |
496 | Using index |
| 13 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 1 | Using filesort |
| 12 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 11 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 10 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 9 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 8 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 7 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 6 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 5 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
| 4 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 | yohan.ST.stock_id
| 137560 | Using filesort |
+----+--------------------+-------------+-------+---------------+---------+---------+-------------------+--------+----------------+
12 rows in set (30.52 sec)
EXPLAIN SELECT * FROM stock_price_history WHERE
stock_id IN (SELECT stock_id FROM etf_stocks WHERE
etf_id = 397);
+----+--------------------+-------------+--------+------------------+---------+---------+-------------------+--------+--------------------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
+----+--------------------+-------------+--------+------------------+---------+---------+-------------------+--------+--------------------------+
| 1 | PRIMARY | <derived4> | ALL |
NULL | NULL | NULL | NULL
| 494 | Using where |
| 4 | DERIVED | ST | index |
NULL | PRIMARY | 4 | NULL
| 496 | Using index |
| 14 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 1 | Using filesort
|
| 13 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 12 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 11 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 10 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 9 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 8 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 7 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 6 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 5 | DEPENDENT SUBQUERY | stockprices | ALL |
PRIMARY | PRIMARY | 4 |
yohan.ST.stock_id | 137560 | Using filesort
|
| 2 | DEPENDENT SUBQUERY | etf_stocks | eq_ref |
PRIMARY,stock_id | PRIMARY | 8 | const,func
| 1 | Using where; Using index |
+----+--------------------+-------------+--------+------------------+---------+---------+-------------------+--------+--------------------------+
13 rows in set (30.34 sec)
SELECT A1.stock_id,
A1.price_date,
A1.adjusted,
A2.price_date AS pd22,
100.0 * (A1.adjusted -
A2.adjusted)/A2.adjusted AS gl22pc,
A3.price_date AS pd66,
100.0 * (A1.adjusted -
A3.adjusted)/A3.adjusted AS gl66pc,
A4.price_date AS pd132,
100.0 * (A1.adjusted -
A4.adjusted)/A4.adjusted AS gl132pc,
A5.price_date AS pd264,
100.0 * (A1.adjusted -
A5.adjusted)/A5.adjusted AS gl264pc
FROM
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1) AS A1
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 22) AS A2
ON A1.stock_id = A2.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 66) AS A3
ON A1.stock_id = A3.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 132) AS A4
ON A1.stock_id = A4.stock_id
LEFT JOIN
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER
BY price_date DESC LIMIT 1 OFFSET 264) AS A5
ON A1.stock_id = A5.stock_id;
+----------+------------+----------+------------+------------+------------+------------+------------+------------+------------+-----------+
| stock_id | price_date | adjusted | pd22 |
gl22pc | pd66 | gl66pc | pd132 |
gl132pc | pd264 | gl264pc |
+----------+------------+----------+------------+------------+------------+------------+------------+------------+------------+-----------+
| 1 | 2007-11-13 | 46.10 | 2007-10-12 |
-5.2025499 | 2007-08-10 | -3.5564854 | 2007-05-08 |
-8.4409136 | 2006-10-25 | 5.0353156 |
+----------+------------+----------+------------+------------+------------+------------+------------+------------+------------+-----------+
1 row in set (0.03 sec)
SELECT * FROM stock_price_history WHERE stock_id = 1;
+----------+------------+---------------+------------+------------+------------+------------+------------+------------+------------+-----------+
| stock_id | pd1 | current_price | pd22 |
gl22pc | pd66 | gl66pc | pd132 |
gl132pc | pd264 | gl264pc |
+----------+------------+---------------+------------+------------+------------+------------+------------+------------+------------+-----------+
| 1 | 2007-11-13 | 46.10 | 2007-10-12 |
-5.2025499 | 2007-08-10 | -3.5564854 | 2007-05-08 |
-8.4409136 | 2006-10-25 | 5.0353156 |
+----------+------------+---------------+------------+------------+------------+------------+------------+------------+------------+-----------+
1 row in set (30.44 sec)
SELECT * FROM stock_price_history WHERE stock_id IN
(SELECT stock_id FROM etf_stocks WHERE etf_id = 397);
+----------+------------+---------------+------------+-------------+------------+-------------+------------+-------------+------------+-------------+
| stock_id | pd1 | current_price | pd22 |
gl22pc | pd66 | gl66pc | pd132 |
gl132pc | pd264 | gl264pc |
+----------+------------+---------------+------------+-------------+------------+-------------+------------+-------------+------------+-------------+
| 1 | 2007-11-13 | 46.10 | 2007-10-12 |
-5.2025499 | 2007-08-10 | -3.5564854 | 2007-05-08 |
-8.4409136 | 2006-10-25 | 5.0353156 |
| 2 | 2007-11-13 | 41.26 | 2007-10-12 |
-11.2688172 | 2007-08-10 | -0.6740491 | 2007-05-08 |
-7.4680422 | 2006-10-25 | 6.5599174 |
| 3 | 2007-11-13 | 93.70 | 2007-10-12 |
0.1710498 | 2007-08-10 | 37.5513799 | 2007-05-08 |
59.7340607 | 2006-10-25 | 117.8563125 |
| 4 | 2007-11-13 | 101.38 | 2007-10-12 |
-12.9187425 | 2007-08-10 | 17.5692914 | 2007-05-08 |
42.6079617 | 2006-10-25 | 72.4442932 |
| 5 | 2007-11-13 | 36.63 | 2007-10-12 |
-3.5291019 | 2007-08-10 | 6.0509554 | 2007-05-08 |
-6.4367816 | 2006-10-25 | 35.3658537 |
| 6 | 2007-11-13 | 82.08 | 2007-10-12 |
-1.7829365 | 2007-08-10 | 2.6641651 | 2007-05-08 |
25.0266565 | 2006-10-25 | 40.3316806 |
| 7 | 2007-11-13 | 97.13 | 2007-10-12 |
-0.1233933 | 2007-08-10 | 10.1122322 | 2007-05-08 |
25.6532988 | 2006-10-25 | 44.1525675 |
| 8 | 2007-11-13 | 50.92 | 2007-10-12 |
7.0422535 | 2007-08-10 | 21.7890457 | 2007-05-08 |
22.4627225 | 2006-10-25 | 15.1515152 |
| 9 | 2007-11-13 | 53.30 | 2007-10-12 |
-5.1770148 | 2007-08-10 | 2.8957529 | 2007-05-08 |
-17.4539260 | 2006-10-25 | -6.1289186 |
| 10 | 2007-11-13 | 34.53 | 2007-10-12 |
-3.2230942 | 2007-08-10 | 3.1362007 | 2007-05-08 |
-8.3841868 | 2006-10-25 | 6.4426634 |
| 11 | 2007-11-13 | 73.99 | 2007-10-12 |
1.6346154 | 2007-08-10 | 12.3102611 | 2007-05-08 |
-8.0983729 | 2006-10-25 | 21.1163857 |
| 12 | 2007-11-13 | 68.60 | 2007-10-12 |
-12.6114650 | 2007-08-10 | -7.1969697 | 2007-05-08 |
-7.0712544 | 2006-10-25 | 3.4378770 |
| 13 | 2007-11-13 | 90.85 | 2007-10-12 |
-14.1709967 | 2007-08-10 | 5.2967084 | 2007-05-08 |
-17.3715325 | 2006-10-25 | 32.9382499 |
| 14 | 2007-11-13 | 90.91 | 2007-10-12 |
-9.1263495 | 2007-08-10 | -8.6698815 | 2007-05-08 |
-19.6695237 | 2006-10-25 | 11.7242227 |
| 15 | 2007-11-13 | 83.82 | 2007-10-12 |
-9.2071057 | 2007-08-10 | -12.3588457 | 2007-05-08 |
-28.4812287 | 2006-10-25 | 6.4110702 |
| 16 | 2007-11-13 | 48.82 | 2007-10-12 |
-10.5697014 | 2007-08-10 | -12.7279228 | 2007-05-08 |
-5.8074474 | 2006-10-25 | -2.2231124 |
| 17 | 2007-11-13 | 46.68 | 2007-10-12 |
-0.8917197 | 2007-08-10 | 11.7013640 | 2007-05-08 |
11.5145724 | 2006-10-25 | 5.0168729 |
| 18 | 2007-11-13 | 52.00 | 2007-10-12 |
2.4832479 | 2007-08-10 | 9.9598224 | 2007-05-08 |
22.0943884 | 2006-10-25 | 36.6982124 |
| 19 | 2007-11-13 | 48.87 | 2007-10-12 |
-12.9032258 | 2007-08-10 | -12.9497684 | 2007-05-08 |
-19.8195242 | 2006-10-25 | 28.5038128 |
| 20 | 2007-11-13 | 31.72 | 2007-10-12 |
0.2211690 | 2007-08-10 | 1.9607843 | 2007-05-08 |
-0.1887980 | 2006-10-25 | 21.6724204 |
| 21 | 2007-11-13 | 64.25 | 2007-10-12 |
-5.5424875 | 2007-08-10 | -4.4467579 | 2007-05-08 |
-1.7584098 | 2006-10-25 | 14.9579531 |
| 22 | 2007-11-13 | 46.99 | 2007-10-12 |
-9.9635946 | 2007-08-10 | -9.3382211 | 2007-05-08 |
-9.0926678 | 2006-10-25 | 13.8599467 |
| 23 | 2007-11-13 | 24.56 | 2007-10-12 |
-7.4604371 | 2007-08-10 | -6.7223699 | 2007-05-08 |
-24.9618087 | 2006-10-25 | -15.1933702 |
| 24 | 2007-11-13 | 49.80 | 2007-10-12 |
-6.0554612 | 2007-08-10 | 1.6949153 | 2007-05-08 |
-2.9807130 | 2006-10-25 | 20.3189176 |
| 25 | 2007-11-13 | 26.65 | 2007-10-12 |
-14.3362263 | 2007-08-10 | -9.9966228 | 2007-05-08 |
-22.2578763 | 2006-10-25 | -12.1332015 |
| 26 | 2007-11-13 | 53.25 | 2007-10-12 |
-19.9248120 | 2007-08-10 | -3.3399891 | 2007-05-08 |
-9.5157179 | 2006-10-25 | -20.8649131 |
| 27 | 2007-11-13 | 29.19 | 2007-10-12 |
2.9266573 | 2007-08-10 | -2.9587766 | 2007-05-08 |
-11.1685940 | 2006-10-25 | -10.1293103 |
| 28 | 2007-11-13 | 19.22 | 2007-10-12 |
-10.2707750 | 2007-08-10 | -2.4365482 | 2007-05-08 |
-0.6204757 | 2006-10-25 | 10.0801833 |
+----------+------------+---------------+------------+-------------+------------+-------------+------------+-------------+------------+-------------+
28 rows in set (30.39 sec)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
