Re: Multiple Tables Split By Date

[Billy Simons]

Hi Rick,

thank you for the reply. Summary tables are not really a good option as we would need so many of them (we did look at this, but the amount that were required meant that we ended up using more space etc).

Partitioning could be an option, but im not too familiar with this. I have read up on it, as it seemed to be the most immediately viable. Does this work well with MASTER-MASTER replication, and if we change the table on one MASTER does it replicate the change to the SLAVE, or is there a proper procedure to follow?

InnoDB isnt something we have looked at, as we have had problems with it in the past whereby it uses a lot of disk space even when you remove records etc. Most likely down to inexperience, but MyISAM is our preferred route.

How else would you suggest doing the pagination? We are using PHP and SELECT SQL_FOUND_ROWS to get the total record count, with limits etc to calculate pagination. We are not familiar with an alternative way, so maybe this is the missing link.

In summary, i think partitioning is the way to go, but I'm unclear on the effects it will have with replication (if any) and the best way to move the current table to a partitioned one.

thanks in advance for any help!