Re: RAM requirements- new server

[Rick James]

There is virtually no reason to split up a large table (by rows). It may feel like you are gaining some kind of efficiency, but you are just trading off some index work vs work to pick the table.

The one big use in PARTITION is to facilitate purging old data. This is where you break up the data by, say, days. Then, every night, you drop the oldest partition and create a new one.

That was "horizontal partitioning". Vertical partitioning is sometimes useful for a different reason...

Simple example: Images. Each row has a lot of meta info about the image, plus a BLOB for the image. Let's say you have some complex queries that gather statistics about the images (but don't need to touch the blobs). Such queries would be stumbling over the blobs. Performance enhancement: Split the table in two -- one for the meta info (much narrower, more easily cachable, etc), and one for the blobs. The latter would be reached into much less frequently (and probably via a JOIN), when you need to display an image(s).