Hourly processing -- Is it munching the same old data over and over? If so, suggest you create a "summary" table, perhaps hourly. This table would be summary info, keyed by the hour and userid (or whatever). Then the hourly report could run from this table, and not have to scan 500K rows. This could easily make the reports run 10x faster. The hourly processing of the last hour's data would be in the background, and relatively insignificant. The pattern is something like:
PRIMARY KEY(hr, usr)
INSERT INTO summary
SELECT truncate-datetime-to-hour AS hr,
usr, sum(...), count(*)
FROM main
WHERE datetime BETWEEN ...
GROUP BY hr, usr;
When you run a query that hits only columns that are in a single index, then only the index needs to be read. This is noticeably faster. (EXPLAIN will say "Using index".) (In InnoDB, this note does not apply to the PRIMARY KEY, which is embedded in the data.)
When it uses an index to find other columns, it has to reach over into the data to fetch them. This (in InnoDB) involves taking the PRIMARY KEY (which is implicitly in every secondary index) and doing a lookup in the data. If there is too much data to be cached, this could be disk hit(s); if it all fits, it is still some CPU effort.
Your example of running much faster the second time is because of caching of index and/or data blocks in the "buffer pool". With the Query cache on, and SQL_CACHE requested, it _could_ run almost instantly. (There are several caveats here. In general I do not recommend using the Query cache.)
512M = 524k Kib; but how much RAM is available? Could you make the buffer pool bigger? Is the 4GB VM exclusive to MySQL? Is there "overcommit"? Are you running only InnoDB, no MyISAM? Then recommend key_buffer_size = 40M and innodb_buffer_pool_size = 2500M, perhaps less if the entire dataset is much less than that.
Why use VM at all? As you noticed, it can add some overhead.
If you consider splitting the table, consider PARTITION.
Yes, you have valid reasons for using mysql_unbuffered_query. I'm curious; what percentage of the total (query execution + result processing) time does buffered/unbuffered cost/save?