How to improve performance of mysql query?

Question

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 ACTION\n) ENGINE=InnoDB AUTO_INCREMENT=1290353 DEFAULT CHARSET=utf8'

Output of SHOW INDEX from access_history;

Output of EXPLAIN query

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

Appreciate any help! Thanks!

Your join and outer where conditions are identical. I would first remove the second condition from the join clause, and the first condition from the where clause. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 21, 2016 at 5:14
There are some repeating criteria in your WHERE clause. Not sure whether it's necessary in your case. Would be good if you can provide sample data. — ydoow
– ydoow, Commented Jun 21, 2016 at 5:35
@TimBiegeleisen: Thanks for immediate response! I tried what you suggested but the result is still same. — Rahul Hendawe
– Rahul Hendawe, Commented Jun 21, 2016 at 5:36
Please also add SHOW INDEX from access_history and the output of EXPLAIN <your query>. — Paul Wasilewski
– Paul Wasilewski, Commented Jun 21, 2016 at 6:52

IVO GELOV · Accepted Answer · 2016-08-05 09:09:03Z

There are 2 variants here:

1) you execute the discussed query periodically by CRON - so you want to update all products whose access history was changed within the last 90 days. Try the following query (you'd better compute the starting date in advance and then use this constant value - using DATE_SUB() will make MySQL to think this is a dynamic value and will compute it each time, will not notice that it is essentially a constant):

UPDATE org_products op
  SET views = (SELECT COUNT(*) FROM access_history
        WHERE access_key = op.id
            AND access_type = "OrgProduct.View"
    )
    WHERE op.id IN (SELECT access_key FROM access_history 
        WHERE access_type = "OrgProduct.View"
            AND created_at >= "YYYY-MM-DD"
    )

2) you execute the discussed query after each insert to table access_history - try the following (ACCESS_KEY_VALUE is what you have just inserted into table access_history):

UPDATE org_products op
    SET views = (SELECT COUNT(*) FROM access_history
        WHERE access_key = ACCESS_KEY_VALUE
            AND access_type = "OrgProduct.View"
    )
    WHERE op.id = ACCESS_KEY_VALUE

In both cases, I recommend you to move all distinct strings from column access_type into separate table, convert the column into INT and make it a foreign key into that new table. The reason is that indexing and comparing numeric values is faster than comparing strings.

Collectives™ on Stack Overflow

How to improve performance of mysql query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related