Mikhail Berman wrote:
Hi Jeremy,
This is still "work in progress" but here are some samples of queries we
will be running, that involved this table and this date field:
==========================================
#this fails -- join on is horrible
What do you mean by "fails"? Takes too long? Wrong results? Crashes
client/server?
In general, using ON for your JOIN conditions is better than putting them in the
WHERE clause. It's never worse for equivalent queries.
update COMPANY_NUMBERS left join TICKER_HISTORY_PRICE_DATA
on ticker = price_data_ticker
and date_sub(date_qtr, interval 0 day) = price_data_date
Huh? Why not "date_qtr = price_data_date"? DATE_SUB is doing nothing useful
here, but is taking time to execute on every row. Also, if there's an index on
date_qtr, you just prevented its use by running date_qtr through a function.
and !isnull(price_data_ticker)
and isnull(price_date)
set price_date = price_data_date,
price_open = price_data_open,
price_close = price_data_close,
price_high = price_data_high,
price_low = price_data_low,
price_date_volume = price_data_volume;
#this succeeds -- putting the on clause in the where is fine -- using
join on is horrible
But this is a different query! This is a JOIN, not a LEFT JOIN, so you cannot
directly compare them. The difference between them is the type of JOIN, not
the location of the join conditions.
update COMPANY_NUMBERS, TICKER_HISTORY_PRICE_DATA
set price_date = price_data_date,
price_open = price_data_open,
price_close = price_data_close,
price_high = price_data_high,
price_low = price_data_low,
price_date_volume = price_data_volume
where
isnull(price_date)
and ticker = price_data_ticker
and date_sub(date_qtr, interval 0 day) = price_data_date
and !isnull(price_data_ticker);
In the first query, you give *all* your restrictions as join conditions, but in
the second, they are all in the WHERE clause, so mysql will choose which to use
as join conditions. These are not equivalent queries, so I expect they give
different results. If you would describe exactly what you want this to do, I'm
sure someone could help you get the right query.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
