Re: How should I partition this table?
> Would it be ok to send you a sample of the select statements priavately?
584886 (at) rjweb.org
> Having them in an open forum might cause issues on this end.
Or it might provide ammunition in the battle. Some companies think it is a great idea to split duties between developers and system maintainers. At some level it is a good idea. For small databases, it does not matter. For a billion rows, any performance "fix" is likely to involve multiple groups.
One group says "faster hardware is the solution"; so they get approval for the money, expend the effort on migrating the data, only to find that that was just a partial solution.
The next group says "tune it". Well, that takes care of only a few aspects. And, again, it is only a partial solution.
The developers are likely to be the most reluctant to work on the problem, partially because it (usually) takes more effort. The simple act of normalizing causes upheaval in multiple spots in their code. So would combining a DATE & TIME into a single field.
One anecdote: The CPU was running constantly at 40%. No hardware or tuning would make much difference. Changing
WHERE DATE(dt) = '2011-11-11'
into
WHERE dt >= '2011-11-11' AND dt < '2011-11-11' + INTERVAL 1 DAY
brought the CPU down to 2%.
The developers did not baulk at the one-line change that I handed them. (Your problem is much more than a 1-liner.)
Another: "Please tune this system so this online query won't take an hour." Well, much _coding_ later, it was down to 2 seconds, but it involved changing the table definitions, revamping the data-loading code, creating summary tables, and rewriting the UI. Hardware and tuning were insignificant contributors to the improvement. (This involved a dataset about as big as yours.)