One thing that has to be taken into consideration is how MySQL uses buffers for its major storage engines: InnoDB and MyISAM.

What lies cached in memory differs greatly between these storage engines.

InnoDB caches both data and index pages. They are loaded into the InnoDB Buffer Pool, which is sized by innodb_buffer_pool_size.

MyISAM caches only index pages and they are loaded into the Key Cache (Key Buffer), which is sized by key_buffer_size.

You must use information_schema.tables to get the data and index sizes occupied on disk in order to size the InnoDB Buffer Pool and MyISAM Key Cache correctly.

Depending on on how much data you have and how much time you will allow, you can warm the caches as follows:

For every table TableT

• goto to each index NDX
• for each index NDX
  • run SELECT every column in NDX,at least one column not indexed in TableT from TableT

By doing this you guarantee that every data and index page gets read at least once. They will sit in the cache. This concept is practiced, in part and in principle, by Percona. Percona built this concept into mk-slave-prefetch. What this program does is

• read relay logs on a slave ahead of the slave processing the SQL in it
• take an SQL statement from the relay log and convert it into a SELECT utilizing the WHERE, GROUP BY, and ORDER BY clauses as a guide to choosing indexes
• execute the SELECT statement that came from converted SQL

This forces the slave to have 99.99% of the data needed by the slave to process the SQL quickly. This also makes the slave prepared in the event you manually failover to the slave and promote it to a master WHOSE CACHES ARE JUST ABOUT THE SAME AS THE MASTER YOU FAILED OVER FROM.

CONCLUSION

Nothing beats having caches ready, willing, and able for you to use in an environment of heavy INSERTS, UPDATEs, and DELETEs.

Give it a Try !!!

CAVEAT

With the birth of products like memcached, some have gotten away from the need to perform proper tuning of MySQL. Granted, many sites benefit from the boost in data retrieval provided by controlling the caching behavior of data as developers have quickly seen with memcached. Many other sites, just by switching storages engines or correctly configuring MySQL, have realized the same performance benefits. Before giving up on the database and strictly using it as a repository, make the most of the your database. Follow through on the due diligence and you might be pleasantly surprised what MySQL will do for you.

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.