Ted Byers wrote:
Thanks Shane,
It works reasonably well. It gets the right answer,
but I guess my data set is much larger than your test.
What indexes have you got?
Using this index on the sample I sent gets the response time to about
5ms (per stock_id) (as opposed to 900ms with these colum
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 a
Shane Ambler wrote:
> I INSERTed 500 stocks entries and 10,000 stockprices entries for each
stock (that's 5,000,000 price rows), then from
EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id = 20
I got - Total runtime: 981.618 ms
EXPLAIN ANALYSE SELECT * FROM stock_price_history W
Ted Byers wrote:
It gave apparently correct values, but for some
reason, it insisted on returning thousands upon
thousands of identical record. There is something
awry there, but I can't place what. Yes, I know I
could use SELECT DISTINCT, but I worry that it may be
doing a full table scan, as
--- Shane Ambler <[EMAIL PROTECTED]> wrote:
> Ted Byers wrote:
> > Please consider the following statement (it
> becomes
> > obvious if you remember the important thing about
> the
> > table is that it has columns for each of stock_id,
> > price_date, and price).
> >
> > (SELECT * FROM stockpric
Ted Byers wrote:
Please consider the following statement (it becomes
obvious if you remember the important thing about the
table is that it has columns for each of stock_id,
price_date, and price).
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY
price_date DESC LIMIT 1)
UNION
(SELECT * FR
Please consider the following statement (it becomes
obvious if you remember the important thing about the
table is that it has columns for each of stock_id,
price_date, and price).
(SELECT * FROM stockprices WHERE stock_id = 1 ORDER BY
price_date DESC LIMIT 1)
UNION
(SELECT * FROM (SELECT * FROM s
