Re: Partitioning dilemma

[Rick James]

Let's study this from a few different points of view...

* Accesses -- Do you "always" access "recent" rows? Or, to phrase another way, how often do you need to mess up the caching by reaching back into old partitions?

* Keys -- Remember the contorted rules of PRIMARY KEYs in PARTITION. AUTO_INCREMENT is somewhere between clumsy and/or unusable.

* ID vs Date "locality of reference" -- Are you using something like AUTO_INCREMENT for session ids and for transaction ids. If so, then all three things (Date, session_id, and xact_id) tend to grow at the "end" of the table. That is, indexes on any of these, plus the data itself, would tend to grow on the end. One hot spot each -- this is _very_ cachable.

* IDs -- AUTO_INCREMENT may have good locality; GUID has terrible.

* Will you be DELETEing rows? MyISAM or InnoDB? Partitioning by Date, then using ALTER PARTITION to do DELETEs? The last of these is very efficient and very fast. Any other deletion; well, let's discuss the ramifications on performance and locality.

* How long does a session last? -- If usually less than a day, that helps with the "locality".

* Efficiency -- In the best of worlds, only the last few blocks of data and of each index need be kept in cache, and performance shines. In the worst of worlds, performance will be terrible.

* Entire PK in cache -- may not be necessary.

* MyISAM or InnoDB?

Answer some of these questions and we can dig into details of how well PARTITION will perform, and even whether it will be any better than a pair of huge tables.