List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 18 2005 5:30pm
Subject:Re: A "key" question
View as plain text  
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
Thread
A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJasper Bryant-Greene17 Nov
  • Re: A "key" questionPeter Brawley17 Nov
RE: A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJeremy Cole17 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionJeremy Cole18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionDavid Griffiths18 Nov
  • Connection ProblemTripp Bishop18 Nov
    • Re: Connection ProblemMichael Stassen18 Nov
      • Re: Connection ProblemTripp Bishop18 Nov