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:[EMAIL PROTECTED]
Sent: Friday, November 18, 2005 11:01 AM
To: Mikhail Berman
Cc: Jasper Bryant-Greene; mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
