brotherhood_of_LAN - 7:56 pm on Mar 5, 2011 (gmt 0)

A couiple other things you can try

SHOW TABLE STATUS LIKE 'table_name'

The 'data free' column will tell you how much wasted space there is in the table. Compare it ot the data size. If it's continually getting bigger then you'll want to OPTIMIZE at some point or the table will gradually slow.

RE: innodb, I would suggest it as a whole MyISAM table is locked when you're updating/deleting (and INSERTs perhaps as you say (MySQL manual) [dev.mysql.com] ).

For MyISAM you could partition on userid (since your queries use that all the time) and if you don't have queries that return lots of random userid's as partitioning can be slower that way. You can optimize partitions and since they'd be smaller, would be quicker to optimize on their own. Also, PK lookups would be faster, including for data manipulation as not all 50M PK's would have to be considered to begin with.