MySQL Forums
Forum List  »  Performance

Re: MYSQL Update performance
Posted by: sree iyer
Date: May 01, 2017 10:45AM


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.

Options: ReplyQuote


Subject
Views
Written By
Posted
132
April 24, 2017 02:11PM
57
April 24, 2017 04:05PM
48
May 01, 2017 08:50AM
Re: MYSQL Update performance
45
May 01, 2017 10:45AM
90
May 01, 2017 01:11PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.