I'm building a rather large table that includes a particular column,
my_column. There are a small-ish number of possible values for
my_column, and each unique value of
my_column should have the same number of rows associated with it. Furthermore, it is likely that queries will only be focused on a single, specific value of
my_column at a time. I see that one option would be to partition this large table based upon the values of
my_column. The other option as I see it would be to create a separate table entirely such that each table associates with exactly one value of
my_column. I'd like to know what would lead me to pick one option over the other.
As far as I understand it, one large table that is partitioned seems like the "correct" or "pure" way to do it, as that seems to be a primary use-case for partitions. However, I suspect that for practical reasons the second option is better, and that's what I'm leaning towards. I don't know if it matters, but I'm dealing with MyISAM tables.