brotherhood_of_LAN - 6:02 pm on Mar 4, 2011 (gmt 0)
If all your SELECT queries use the playdata column I'd consider moving that to the start of the PK and changing the table to InnoDB. If you are able to, benchmark them to compare. If you're familiar with PHP or other scripting you can see how many queries per second each setup can do to compare for SELECT speed. You can also fork the script to simulate lots of users.
The reason for changing the PK order is that they would be somewhat in ascending order (date is always increasing, userid may be random). I'd also leave the variable length column as is with innodb.
Regarding partitioning... it would split your table up into sub-tables and could speed up a simple OPTIMIZE. Again, if your queries all include the playdata column, partition by DATE... making sure none of your regularly performed queries use more than a couple partitions, preferably one. SELECTing from 100,000 rows is much better than a million.
As I understand, new inserts would will the gap, and since table format is fixed, the new records would fit the gap completely and there wouldn't be any more fragmentation
I think that's right...
It's really worth testing a few combinations and seeing what works for your particular case.