I have a large MySQL table of about 50 million records. The table is heavy with inserts, updates and selects (about 150 per second). I'm considering to change the format from dynamic to fixed(static) to improve performance. The only varchar column is 12 characters, so it shouldn't add much to the size of the table.
A few times during the day, a couple of records are deleted from this table. Not much, maybe 10 or 20 records. I read everywhere that MyISAM supports concurrent INSERT and SELECT if there are no deleted rows in middle of the table. I assume this is due to the fragmentation of the table. What I failed to find out is whether the fixed table format is a good workaround if you delete a few records from time to time. 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. Since deletes are rare and only a few records is deleted, I assume the gap would be filled really fast (in a couple of seconds) and I would have concurrent inserts all the time. Do you know of any article, of have a personal experience confirming this?