Fastest engine for Mass Updates (only updates done on table)
Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row

In that answer, I recommended the following


Adding a third column to the table to represent a slot
Select an arbitrary value for the number of slots
Making a composite PRIMARY KEY


hour boundary
slot number

Doing an INSERT with ON DUPLICATE KEY UPDATE to increment the counter


Since you are doing 30-40 million updates, you may need to choose a very high number of slots. In your case, I would go with 250,000 slots. The high the number of slots, the lower the incidence of collision (trying to update the same slot).

As far as the choice of storage engine, you cannot stay with MyISAM. Why ? MyISAM will do a full table lock for every update. Doing 40 million updates require 40 million table locks. My posted answer works well with InnoDB's row level locking. Even if you implement the slot paradigm, MyISAM will still do 40 million table locks with 40 million updates.

Definitely, you must go with InnoDB. You also need to tune InnoDB.


Larger Buffer Pool (increasing innodb_buffer_pool_size)
Larger Log Buffer (increasing innodb_log_buffer_size to 128M) 
More Write I/O Threads (setting innodb_write_io_threads to 8 or more)


If you are using MySQL 5.1, upgrade to MySQL 5.6.

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

I learned the concept of slots for counters from page 140 of the Book



My post (dated Sep 06, 2013) is the first post in the DBA StackExchange to mention this book for your specific issue. 
 Someone else mentioned this technique one month later in this same forum (counter table in mysql dates Oct 18, 2013)

I just tried googling for this concept search for mysql counter slot

I found some posts:


Baron Schwartz, one of the authors of the book, has a URL to that very reference
Avoid locks when storing counters in MySQL


The main point I am making is that this concept is not new. You can implement it.

Give it a Try !!!