I am having a performance problem with my current single instance database as the volumes of data grow. The application I am working on receives inserts once a month, and the oldest month is deleted at the same time. The number of rows grows in the hundred millions for the most accessed table (one row per item per month per store) and is joined on a table storing the data specific to items regardless of time (like its label).
A normal query acts as follow: Do a aggregation either per month, store or article over a different amount of store/months and categories.
I was looking at some sharding possibilities that would allow me to keep the queries as they are (I can't afford to rewrite everything for now) and run the queries exactly as if I had only one server but in the end that server would run the query on multiple shards/replications to speed things up.
Can I expect any good speed increase on currenly slow queries? So far my understanding of cluster/sharding is mostly to help reduce loads from many read/writes but this is not a problem at all for me at this time since there is not a lot of queries going on (rarely multiple queries at the same time on the server instance).
EDIT: I'd like to point out that today, I am coping with ok speeds by having the full table in memory so I've removed the disk read times, so simply storing the data on different disks wouldn't solve all...