List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:November 18 2005 4:24pm
Subject:RE: A "key" question
View as plain text  
 
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

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 
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
 

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);

 
mysql> describe COMPANY_NUMBERS;
+---------------------------------------------------------------+-------
-----+------+-----+------------+-------+
| Field                                                         | Type
| Null | Key | Default    | Extra |
+---------------------------------------------------------------+-------
-----+------+-----+------------+-------+
| company_fkey                                                  |
char(10)   |      | PRI |            |       |
| company_name                                                  |
char(150)  |      | MUL |            |       |
| ticker                                                        |
char(8)    |      | MUL |            |       |
| market                                                        |
char(20)   |      | MUL |            |       |
| price_date                                                    | date
| YES  |     | NULL       |       |
| price_open                                                    | float
| YES  |     | NULL       |       |
| price_close                                                   | float
| YES  |     | NULL       |       |
| price_high                                                    | float
| YES  |     | NULL       |       |
| price_low                                                     | float
| YES  |     | NULL       |       |
| price_date_volume                                             | float
| YES  |     | NULL       |       |
| total_shares_outstanding_date                                 | date
| YES  |     | NULL       |       |
| total_shares_outstanding                                      |
bigint(20) | YES  |     | NULL       |       |
| total_shares_outstanding_market_cap                           |
bigint(20) | YES  |     | NULL       |       |
| date_qtr                                                      | date
|      | PRI | 0000-00-00 |       |
| date_ttm                                                      | date
| YES  |     | NULL       |       |
| filing_type_code_qtr                                          |
char(12)   |      |     |            |       |
| filing_type_code_ttm                                          |
char(12)   |      |     |            |       |
| annual_quarterly_indicator                                    |
char(1)    |      |     |            |       |
| balsh_book_value                                              |
bigint(20) | YES  |     | NULL       |       |
| balsh_total_assets                                            |
bigint(20) | YES  |     | NULL       |       |
| balsh_cash_and_cash_equivalents                               |
bigint(20) | YES  |     | NULL       |       |
| incmst_revenue_qtr                                            |
bigint(20) | YES  |     | NULL       |       |
| incmst_revenue_ttm                                            |
bigint(20) | YES  |     | NULL       |       |
| incmst_net_income_qtr                                         |
bigint(20) | YES  |     | NULL       |       |
| incmst_net_income_ttm                                         |
bigint(20) | YES  |     | NULL       |       |
| incmst_extraordinary_items_qtr                                |
bigint(20) | YES  |     | NULL       |       |
| incmst_extraordinary_items_ttm                                |
bigint(20) | YES  |     | NULL       |       |
| incmst_ebitda_qtr                                             |
bigint(20) | YES  |     | NULL       |       |
| incmst_ebitda_ttm                                             |
bigint(20) | YES  |     | NULL       |       |
| incmst_effect_of_accounting_changes_qtr                       |
bigint(20) | YES  |     | NULL       |       |
| incmst_effect_of_accounting_changes_ttm                       |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_operating_activities_qtr                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_operating_activities_ttm                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_investing_activities_qtr                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_investing_activities_ttm                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_financing_activities_qtr                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_from_financing_activities_ttm                |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_flow_change_in_cash_and_cash_equivalents_qtr |
bigint(20) | YES  |     | NULL       |       |
| cshflst_net_cash_flow_change_in_cash_and_cash_equivalents_ttm |
bigint(20) | YES  |     | NULL       |       |
+---------------------------------------------------------------+-------
-----+------+-----+------------+-------+
39 rows in set (0.00 sec)
 
mysql> select count(*) from COMPANY_NUMBERS;
+----------+
| count(*) |
+----------+
|   175102 |
+----------+
1 row in set (0.01 sec)
 
mysql> describe TICKER_HISTORY_PRICE_DATA;
+-------------------+---------+------+-----+------------+-------+
| Field             | Type    | Null | Key | Default    | Extra |
+-------------------+---------+------+-----+------------+-------+
| price_data_ticker | char(8) |      | PRI |            |       |
| price_data_date   | date    |      | PRI | 0000-00-00 |       |
| price_data_open   | float   | YES  |     | NULL       |       |
| price_data_high   | float   | YES  |     | NULL       |       |
| price_data_low    | float   | YES  |     | NULL       |       |
| price_data_close  | float   | YES  |     | NULL       |       |
| price_data_volume | float   | YES  |     | NULL       |       |
+-------------------+---------+------+-----+------------+-------+
7 rows in set (0.00 sec)
 
mysql>

And thank you again,

Mikhail Berman

-----Original Message-----
From: Jeremy Cole [mailto:jcole@stripped] 
Sent: Friday, November 18, 2005 11:01 AM
To: Mikhail Berman
Cc: Jasper Bryant-Greene; mysql@stripped
Subject: Re: A "key" question

Hi Mikhail,

> Thank you for your help.
> 
> I do have an exact situation you have assume I have. Here is the 
> output of SHOW CREATE TABLE
> 
>   `price_data_ticker` char(8) NOT NULL default '',
>   `price_data_date` date NOT NULL default '0000-00-00',

  ...

>   UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`)

  ...

> As you can see, Unique KEY is on two first fields, but most of the 
> work, joins & searches, will be done on the second field
"price_data_date".

Could you provide some example queries?

Likely the solution is to create another index on price_data_date, that
could be used for searches by date that do not include ticker.

As I mentioned before, an index on (a, b) can be used for (a) but not
for (b) alone.  However, it usually doesn't make sense to create an
index on (b, a) as well, since if you have both columns in your query,
usually the index on (a, b) would be fine.  So I would suggest adding an
index:

   ALTER TABLE `TICKER_HISTORY_PRICE_DATA`
     ADD INDEX (price_data_date);

Keep in mind that will lock the table to add the index, and may take a
few minutes (although I would expect less than two minutes for 32M rows)
so it might not be a good idea to run while the market is open. :)

If you could provide the exact query you were running, I could confirm
that it would or would not help. :)

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
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