Q:

Mysql - query table with over 10m data

I am maintaining a web project using Java & mysql.
One mysql table has over 10 million records, I did partition the table by date, so that to reduce rows in each partition. Indexes are also added properly according to queries.
In most query, only the first 1 or 2 partition is used, and the sum of records in those used partitions are less than 200m, it's still pretty quick.
But a few of the queries need to load over 10 partitions to do some statictis, thus over 10m records is involved in a single query, this is quite slow, and it becomes worse as data grows.
Part of the table:
id(int), amount(double), type(varchar), user_id(int), event_date(timestamp)
-- `id` is primary key, `type` has index,

One of the big query is similar to:
select count(id), sum(amount) group by (type)
where event_date between '2014-01-01' and '2014-12-31 23:59:59' and amount >= 10

-- The start & end datetime and the amount range might change.

My question is:
How to make the query that involves over 10m records quicker?
Here is my guess, but not sure:

Use mysql cluster? (I never used that before.)
Use big cache. (Memcache, but the big query is not frequently used.)

A:

There are several other things you can do for performance improvement. 

Analyze the query and introduce indexes as required.   
Identify data access pattern of the application and you can cache only the frequently accessed data to reduce disk I/O. 

