I've only recently began to read about partitions, and I'm not
entirely sure that I understand how it works. Here's where I'm
reading:

http://dev.mysql.com/tech-resources/...artitions.html

I have a message board, and the posts are kept in a table. This table
currently has about 2 millions rows, and grows by about 3,000 a day.
Obviously, the newest posts are accessed the most, and the older they
are, the less they're accessed.

It's set up like this:

id -> mediumint(9)
subject -> tinytext
postdate -> varchar(14)
username -> varchar(50)
email -> tinytext
comment -> longtext

I'm thinking that the database would run faster if I partitioned it by
the postdate (eg, 20110324214814 is written, for 3/24/2011,
9:48:14pm). Is that correct? If so, how would this be done? Would I
set up a partition for every year, and then check if the postdate
begins with that year?

Further is it better to have more partitions with less rows in each?
Meaning, would it be better to have a partition for each month of a
year?

Or is any of this even relevant for a table with 2 million rows?

On Mar 24, 9:52*pm, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:
your last assessment may be the answer to your question. That being
said, what is your goal in using partitions? What is your archival/
retention period going to be? If it is just to make it run faster,
you might want to partition it a little finer than YEAR. (3000*365)
is approximately 1M posts/year - maybe you want to go down to
1month??

The obligatory RT*M:
For partitioning by date - and getting around the INTEGER-only column
restriction see:
http://dev.mysql.com/tech-resources/...ith_dates.html
and
http://dev.mysql.com/doc/refman/5.1/...mitations.html
[so, you want an index on that partition]

Depending on your platform, you may want to place each partition in a
different datafile on a different filesystem/device (and don't use
partitioned devices - that just make no sense what-so-ever).