Good afternoon all. I am coming to you in the hopes that you can provide some direction with a MYSQL optimization problem that I am having. First, a few system specifications.

* MYSQL version: 5.2.47 CE
* WampServer v 2.2

Computer: 

* Samsung QX410 (laptop)
* Windows 7
* Intel i5 (2.67 Ghz)
* 4GB RAM

I have two tables:

1. “Delta\_Shares” contains stock trade data, and contains two columns of note. “Ticker” is Varchar(45), “Date\_Filed” is Date. This table has about 3 million rows (all unique). I have an index on this table “DeltaSharesTickerDateFiled” on (Ticker, Date\_Filed).
2. “Stock\_Data” contains two columns of note. “Ticker” is Varchar(45), “Value\_Date” is Date. This table has about 19 million rows (all unique). I have an index on this table “StockDataIndex” on (Ticker, Value\_Date).

I am attempting to update the “Delta\_Shares” table by looking up information from the Stock\_Data table. **The following query takes more than 4 hours to run.** 

```
update delta_shares A, stock_data B
set A.price_at_file = B.stock_close
where A.ticker = B.ticker
    and A.date_filed = B.value_Date;

```

Is the excessive runtime the natural result of the large number of rows, poor index’ing, a bad machine, bad SQL writing, or all of the above? Please let me know if any additional information would be useful (I am not overly familiar with MYSQL, though this issue has moved me significantly down the path of optimization). I greatly appreciate any thoughts or suggestions.

---

UPDATED with "EXPLAIN SELECT" 

```
1(id)  SIMPLE(seltype)  A(table)   ALL(type)  DeltaSharesTickerDateFiled(possible_keys) ... 3038011(rows)   

1(id)  SIMPLE(seltype)  B(table)  ref(type)  StockDataIndex(possible_keys)  StockDataIndex(key)  52(key_len) 13ffeb2013.A.ticker,13ffeb2013.A.date_filed(ref) 1(rows)   Using where

```

---

UPDATED with table describes.
Stock\_Data Table:

```
idstock_data    int(11)         NO  PRI     auto_increment
ticker          varchar(45)     YES MUL     
value_date      date            YES         
stock_close     decimal(10,2)   YES 

```

Delta\_Shares Table:

```
iddelta_shares          int(11) NO  PRI     auto_increment
cik                     int(11) YES MUL     
ticker              varchar(45) YES MUL     
date_filed_identify     int(11) YES         
Price_At_File       decimal(10,2)   YES         
delta_shares        int(11) YES         
date_filed                date  YES         
marketcomparable            varchar(45)      YES            
market_comparable_price     decimal(10,2)    YES            
industrycomparable          varchar(45)      YES            
industry_comparable_price   decimal(10,2)    YES                    

```

Index from Delta\_Shares:

```
delta_shares    0   PRIMARY 1   iddelta_shares  A   3095057             BTREE       
delta_shares    1   DeltaIndex  1   cik A   18          YES BTREE       
delta_shares    1   DeltaIndex  2   date_filed_identify A   20633           YES BTREE       
delta_shares    1   DeltaSharesAllIndex 1   cik A   18          YES BTREE       
delta_shares    1   DeltaSharesAllIndex 2   ticker  A   619011          YES BTREE       
delta_shares    1   DeltaSharesAllIndex 3   date_filed_identify A   3095057         YES BTREE       
delta_shares    1   DeltaSharesTickerDateFiled  1   ticker  A   11813           YES BTREE       
delta_shares    1   DeltaSharesTickerDateFiled  2   date_filed  A   3095057         YES BTREE       

```

Index from Stock\_Data:

```
stock_data  0   PRIMARY 1   idstock_data    A   18683114                BTREE       
stock_data  1   StockDataIndex  1   ticker  A   14676           YES BTREE       
stock_data  1   StockDataIndex  2   value_date  A   18683114            YES BTREE       

```