mercredi 22 juin 2016

How to improve performance of mysql query?

I am updating a number of rows in a table. The query takes long time to execute. How can I improve the execution performance of below query?

update org_products op
inner join access_history
    on access_history.access_key = op.id and access_history.access_type = "OrgProduct.View"
set views =
(
    select count(access_key)
    from access_history
    where access_history.access_key = op.id and
          access_history.created_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) and
          access_history.access_type = "OrgProduct.View" and
          access_history.product_id = op.product_id
    GROUP BY  access_history.product_id
)
where access_history.access_key = op.id and
      access_history.access_type = "OrgProduct.View";

Update: Output of SHOW CREATE TABLE access_history;

 'access_history', 'CREATE TABLE `access_history` (
n  `id` bigint(20) NOT NULL AUTO_INCREMENT,
n  `product_id` bigint(20) unsigned NOT NULL,
n  `access_type` varchar(50) DEFAULT NULL,
n  `access_key` varchar(50) DEFAULT NULL,
n  `access_key_full` varchar(200) DEFAULT NULL,
n  `client_ip_addr` varbinary(16) DEFAULT NULL,
n  `userid` bigint(20) unsigned DEFAULT NULL,n  `username` tinytext,
n  `anon_user_id` bigint(20) unsigned DEFAULT NULL,
n  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
n  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,n  PRIMARY KEY (`id`),
n  KEY `fk_access_history_has_product_product1_idx` (`product_id`),
n  KEY `idx_access_history_prod_type_key` (`product_id`,`access_type`,`access_key`),
n  CONSTRAINT `fk_access_history_has_product_product1_idx` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON 
DELETE NO ACTION ON UPDATE NO ACTIONn) ENGINE=InnoDB AUTO_INCREMENT=1290353 DEFAULT CHARSET=utf8'

Output of SHOW INDEX from access_history; enter image description here

Output of EXPLAIN query enter image description here

What changes in the query are required to increase the performance?

Appreciate any help! Thanks!

Aucun commentaire:

Enregistrer un commentaire