Re: MYSQL Update performance

[sree iyer]

I executed this query -

select cp.id
from claim_provider_association cp 
join provider_attributes_lookup pa  on  cp.provider_attributes_lookup_id = pa.id
                                and pa.old_provider_id is not null
where cp.provider_id is null;

It is fast - 96,349,880 selected in 734 seconds. The query is till running.
                    


Other Details -
1) MYSQL Version = 5.6.26-74.0-log

2) innodb_buffer_pool_size = 644245094400

3) procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 5  7 4617544 19897876 548164 48111580    0    0   121   263    0    0  4  1 94  2  0


I tried force index on

explain extended
update claim_provider_association cp 
inner join 
     provider_attributes_lookup pa  force index (primary)   
on cp.provider_attributes_lookup_id = pa.id
and pa.old_provider_id is not null
SET cp.provider_id = pa.old_provider_id  
where cp.provider_id is null;

but for some reason MYSQL decides to ignore it.
Here is the explain result -
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+--------------------------+----------+----------+-------------+
| id | select_type | table | type | possible_keys                  | key                            | key_len | ref                      | rows     | filtered | Extra       |
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+--------------------------+----------+----------+-------------+
|  1 | SIMPLE      | pa    | ALL  | PRIMARY                        | NULL                           | NULL    | NULL                     | 13941568 |   100.00 | Using where |
|  1 | SIMPLE      | cp    | ref  | claim_provider_association_idx | claim_provider_association_idx | 4       | claims_master_prod.pa.id |       31 |   100.00 | Using where |
+----+-------------+-------+------+--------------------------------+--------------------------------+---------+--------------------------+----------+----------+-------------+


I'm trying to figure out those 31 rows to see how fast the update is. I will update with my stats on that , once i figure out this.