Post table is too big, how to reduce or split it?

[raywjohnson]

The issue with a large MySQL table is when a new record is added (i.e. the table it written to). The table has to be locked before the new record is added. This takes memory and processor power to accomplish.

Reading data from a table is quick and easy (not nearly as memory or processor intensive).

Moving old records (thread/posts) to a second table (that is read only) allows your active table (read/write) to be small, and your archive table (read only) to be as large as you need.

Also, the use of Sphinx Search will make searching the large table simple (and very fast).

It is not the optimal solution, but for those of us on a budget (and cannot afford to pay for additional MySQL servers w/huge amounts for RAM), this works well.

The large table is still unwieldy for many normal operations. A better solution would be to have a table for every year of threads and posts. Or just divide it by size. Say, 1GB max per archive table.

Since I set this up, my forum and server run smooth and efficient. Even as the archive table has grown to 7GB+.

--RayJ