Updating views_24hours, views_7days, views_30days too slow.

Question

Hello,

I have table (MyISAM) with columns views_total, views_24h, views_7d, views_30d. views_24h = view for last 24 hours, views_7d = last 7 days etc. All this columns are INT and INDEX because I'm doing sorting by them.

Every hour (for 24h), or every day (for 7d, 30d) I'm updating this columns using UPDATE ... JOIN (... UNION ALL ...) USING ... SET ... UPDATE itself takes about 2.5 sec. (100-120K rows), but before updating this columns I should set them to null. So, I'm making UPDATE table SET views_24h = 0 and this query takes about 4 seconds.

Is there any way to speed this UPDATE? Maybe, I should choose another algorithm or another data-model for this purposes? Maybe, I should separate this fields into another table and just do DELETE (but I will cause load during JOIN when displaying)?

I have pretty good NoSQL solution for this (Redis's ZSET datatype), but I want realize first how to do it quickly in MySQL...

Thank you.

Answer 1

You say you have indexes, but I imagine those indexes aren't used for the UPDATE, only for sorting SELECT queries. So those indexes actually decrease performance of the UPDATE.

Usually my advice is that the overhead of indexes during UPDATE is more than justified by their benefit during SELECT. It's probably true in your case too, but you asked about how to speed up the UPDATE so we'll focus on that.

You should analyze the query with EXPLAIN but MySQL doesn't support EXPLAIN UPDATE. So you'll have to do an equivalent SELECT to analyze the optimization:

EXPLAIN SELECT * FROM ... JOIN ( ...UNION ALL... ) USING...

That should show you whether it's using other indexes to help this join. I can't give more specific suggestions since you haven't shown the full UPDATE query, or the full structure of any of your tables, or what other indexes they might have.

Another tip might be to drop your indexes on the view-count columns before the UPDATE, to reduce the overhead. Then rebuild the indexes after the UPDATE. That might make the UPDATE quicker, but dropping and recreating the indexes would be costly too.